August 15, 2017 at 3:56 pm
I know this is illegal with a merge, but is there a way to update the same row twice with a regular Update statement?
CREATE TABLE [#stageTable] (
Year int NOT NULL,
Parcel char(14) NOT NULL,
PropCode char(4) ,
Sequence int,
VG_RIN int,
District int,
DistrictRate Decimal(8,6),
Acres decimal(9,2),
Market int, MktDiff int,
Taxable int,
YearBuilt int,
Size int,
RowType int,
Zone int,
GBCode char(4),
ChangeType char,
ReviewDateTime DateTime
PRIMARY KEY CLUSTERED (Year,Parcel,PropCode,ChangeType) ON [PRIMARY]
) ON [PRIMARY];
Data:
INSERT [#stageTable] (Year, Parcel, VG_RIN, PropCode, MktDiff, Acres, Size, YearBuilt, ChangeType, ReviewDateTime, Market, GBCode) VALUES
(2018,'00-0015-2970',41402,'BR01',47567,0,2222,2005,'R',NULL,235493,''),
(2018,'00-0015-2970',112209,'LA02',20650,4.13,0,0,'R',NULL,123900,''),
(2018,'00-0015-2970',41405,'LR01',10000,0,0,0,'N',NULL,0,''),
(2018,'00-0015-2970',41405,'LR01',-100000,0,0,0,'R',NULL,0,'')
I am using this update to move the data from #stageTable to the TRValue table. Of course it brings in the first 3 rows without a problem, but the last row is skipped without so much as a warning!
UPDATE v SET
v.CurrentMarket = case when v.CurrentMarket + t.MktDiff <= 0 then 0 else v.CurrentMarket + t.MktDiff end,
v.Acres = t.Acres,
v.ChangeType = t.ChangeType,
v.YearBuilt = case when t.YearBuilt > 0 then t.YearBuilt else v.YearBuilt end,
v.Size = case when t.Size > 0 then t.Size else v.Size end
FROM #TRValue v
INNER JOIN #stageTable t
on t.Year = v.Year
and t.VG_RIN = v.RIN
I know it is being skipped because the last 2 rows have the same VG_RIN. But they have different ChangeTypes and the individual ChangeTypes with their corresponding value need to be logged separately.
What other approaches can I try to accomplish this?
Thanks!
Here is the #TRValue DDL with Insert statements. I have modified the Update statement to remove an unnecessary join and table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[#TRValue](
[SysID] [int] IDENTITY(1,1) NOT NULL,
[Year] [int] NOT NULL,
[Parcel] [varchar](13) NOT NULL,
[RIN] [int] NOT NULL DEFAULT ((0)),
[PropCode] [varchar](4) NOT NULL DEFAULT (''),
[PropRIN] [int] NOT NULL DEFAULT ((0)),
[Sequence] [int] NOT NULL DEFAULT ((0)),
[Acres] [decimal](12, 2) NOT NULL DEFAULT ((0)),
[Size] [int] NOT NULL DEFAULT ((0)),
[YearBuilt] [int] NOT NULL DEFAULT ((0)),
[CurrentMarket] [int] NOT NULL DEFAULT ((0)),
[CurrentTaxable] [int] NOT NULL DEFAULT ((0)),
[CurrentTaxAmt] [decimal](12, 2) NOT NULL DEFAULT ((0)),
[ChangeType] [char](1) NOT NULL DEFAULT ('N'),
CONSTRAINT [TRV_YearRINX] PRIMARY KEY CLUSTERED
(
[Year] DESC,
[RIN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [TRV_ParcelNdx] ON [dbo].[#TRValue]
(
[Year] DESC,
[Parcel] ASC,
[PropCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE NONCLUSTERED INDEX [TRV_SysIDX] ON [dbo].[#TRValue]
(
[SysID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET IDENTITY_INSERT #TRValue ON
INSERT #TRValue ([SysID], [Year],[Parcel],[RIN],[PropCode],[PropRIN],[Sequence],[Acres],[Size]
,[YearBuilt],[CurrentMarket],[CurrentTaxable],[CurrentTaxAmt],[ChangeType]) VALUES
(779248, 2018, '00-0015-2970', 41402, 'BR01', 18, 1, 0.00, 2222, 2005, 187926, 103359, 1256.33, 'R'),
(798406, 2018, '00-0015-2970', 112209, 'LA02', 41, 0, 4.13, 0, 0, 103250, 103250, 1255.00, 'R'),
(779249, 2018, '00-0015-2970', 41405, 'LR01', 51, 1, 0.00, 0, 0, 90000, 45000, 601.67, 'R')
SET IDENTITY_INSERT #TRValue OFF
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 15, 2017 at 4:18 pm
Without seeing DDL for the other table I would guess you have to join on ChangType as well.
August 15, 2017 at 4:18 pm
Can you post DDL and sample data for TRValue and TRProp?
We'll need more info in order to help. But best guess is that it isn't actually skipping it, it is getting multiple matched results and just the last one is being saved. Now, "last one" is a relative term as it all depends on how SQL ordered the results and (depending on indexes) may be inconsistent.
You'd need to have the #stageTable match 1 to 1 with the rest of the table joins.
If you do a select on the data instead of the update, you should be able to get an idea of what is actually going on. I expect you will see more than 4 rows.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 15, 2017 at 5:10 pm
I have updated the original post with DDL for #TRValue with 3 rows.
After it is all said and done, I would like to have the LR01 CurrentMarket be 0.
Thanks.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 15, 2017 at 10:02 pm
bmg002 - Tuesday, August 15, 2017 4:18 PMCan you post DDL and sample data for TRValue and TRProp?
We'll need more info in order to help. But best guess is that it isn't actually skipping it, it is getting multiple matched results and just the last one is being saved. Now, "last one" is a relative term as it all depends on how SQL ordered the results and (depending on indexes) may be inconsistent.You'd need to have the #stageTable match 1 to 1 with the rest of the table joins.
If you do a select on the data instead of the update, you should be able to get an idea of what is actually going on. I expect you will see more than 4 rows.
Not to be contrary, but there IS a restriction on a single physical row being updated more than once within any one update statement. It's not limited to MERGE. In order to do what you're expecting you'd have to consider using some form of aggregation to get the correct balance on your table (you'd also have to put some kind of logic to determine what the change type of the final record needs to be).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 16, 2017 at 8:34 am
LinksUp - Tuesday, August 15, 2017 5:10 PMI have updated the original post with DDL for #TRValue with 3 rows.After it is all said and done, I would like to have the LR01 CurrentMarket be 0.
Thanks.
With the updated info, if it was me, I'd add to your last join to join on the changetype. That should solve your issue, no? Otherwise there is no way for SQL to know which row it should be updating when it finds 2 that match based on the join predicates.
My thoughts on the query for the update is that it should be changed to:UPDATE v SET
v.CurrentMarket = case when v.CurrentMarket + t.MktDiff <= 0 then 0 else v.CurrentMarket + t.MktDiff end,
v.Acres = t.Acres,
v.ChangeType = t.ChangeType,
v.YearBuilt = case when t.YearBuilt > 0 then t.YearBuilt else v.YearBuilt end,
v.Size = case when t.Size > 0 then t.Size else v.Size end
FROM #TRValue v
INNER JOIN #stageTable t
on t.Year = v.Year
and t.VG_RIN = v.RIN
AND t.ChangeType = v.ChangeType
Also, Matt Miller (4), I was not trying to say that the row would update twice, moreso that the row would update with an unpredictable value. My understanding was that it would update it multiple times but only the last one would stick (or is it the first? I can never remember). Eiethr way, it isn't always predictable as to what value will be stored when you do an update like that.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 16, 2017 at 10:55 am
bmg002 - Wednesday, August 16, 2017 8:34 AMMy thoughts on the query for the update is that it should be changed to:UPDATE v SET
v.CurrentMarket = case when v.CurrentMarket + t.MktDiff <= 0 then 0 else v.CurrentMarket + t.MktDiff end,
v.Acres = t.Acres,
v.ChangeType = t.ChangeType,
v.YearBuilt = case when t.YearBuilt > 0 then t.YearBuilt else v.YearBuilt end,
v.Size = case when t.Size > 0 then t.Size else v.Size end
FROM #TRValue v
INNER JOIN #stageTable t
on t.Year = v.Year
and t.VG_RIN = v.RIN
AND t.ChangeType = v.ChangeType
Thanks for the suggestion. But, it is not quite so simple as just adding more conditionals to the INNER JOIN. What happens, of course, is that it joins on the transaction which happens to match changeTypes in the main table skipping the one that does not match. Aggregates don't really help me either. The end goal is that both transactions from the stage table have to be logged and that the end result is the CurrentMarket is 0 for the LR01 code.
It looks like I will have to somehow make separate runs.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 16, 2017 at 11:20 am
OH... you could likely sum it over a partition then, no?
It is the t.MktDiff column that you are trying to sum up... what if you change the line:v.CurrentMarket = case when v.CurrentMarket + t.MktDiff <= 0
then 0
else v.CurrentMarket + t.MktDiff
end
tov.CurrentMarket = case when v.CurrentMarket + SUM(t.MktDiff) OVER (PARTITION BY t.Year, t.VG_RIN) <= 0
then 0
else v.CurrentMarket + SUM(t.MktDiff) OVER (PARTITION BY t.Year, t.VG_RIN)
end
Or is that still not quite what you are looking for?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 17, 2017 at 11:34 am
>> I know this is illegal with a MERGE, but is there a way to update the same row twice with a regular UPDATE statement? <<
The short answer is that when you use the old, non-relational, non-standard Sybase UPDATE..FROM.. syntax you are updating the same rows twice! The problem is that only the last update actually gets put in the table, if there is a cardinality error (which the MERGE statement would catch), this syntax will grab whatever value is physically last in the physical storage; so much for logical data model!
It would also help if you didn’t use reserved words for column names (year, sequence, size, etc.) or uselessly vague words, so this table would make some sense. I did a quick edit, following ISO 11179 rules, and added, “_something” or “something_” to your improper or vague data element names. You really ought to go back and correct your data dictionary.
I’m also trying to figure out why the data hasn’t been scrubbed before it’s staged. You have more NULLs in one table than I have had in entire database systems for major corporations.
CREATE TABLE Foobar
(something_year INTEGER NOT NULL, –- reserved word
parcel_something CHAR (14) NOT NULL,
prop_code CHAR (4),
something_sequence INTEGER,
vg_rin INTEGER,
district_something INTEGER,
district_rate DECIMAL(8, 6),
district_acreage DECIMAL(9, 2),
market_something INTEGER,
market_diff INTEGER,
taxable_something INTEGER,
built_year INTEGER,
something_size INTEGER,
row_type INTEGER,
something_zone INTEGER,
gb_code CHAR (4),
change_type CHAR(1),
review_timestamp DATETIME2(0)
PRIMARY KEY (something_year, parcel_something, prop_code, change_type);
Your real problem is that you don’t understand how a set oriented language works. You expected the updates to come in sequentially, as if you were using a deck of punch cards or magnetic tape. But in a set oriented language, everything how things “all at once”, which is why the merge statement would find out that you tried to update the same row twice. This is called cardinality violation.
Please consider the following CASE deck of punch cards or magnetic tape. In 1950 expression:
CASE WHEN T.built_year > 0 THEN T.built_year ELSE V.built_year END
If you could use the NULL, then we would have used more natural COALESCE () expression. But the case expression looks like the if–then–else control flow that you are used to from procedural languages.
>> I know it is being skipped because the last 2 rows have the same vg_rin. But they have different change_types and the individual change_types with their corresponding value need to be logged separately. <<
in SQL, most of the work is done in the DDL and not kludged in the DML. Just as a guess, I think we need to redesign the schema and consider using ETL to scrub the data before it gets into the schema. Are you in a position to do this sort of stuff, or are you screwed?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply