0

Imagin you have a main table like that

DECLARE @ExistentData TABLE (ID INT IDENTITY(1000,1), [Row] INT, [Col] INT, Val NVARCHAR(50));
INSERT INTO @ExistentData([Row], [Col], Val)
VALUES 
(1,10,'Value 1'),
(1,11,'Value 2'),
(1,12,'Value 3.1'),
(1,12,'Value 3.2')

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Value 1
1001        1           11          Value 2
1002        1           12          Value 3.1
1003        1           12          Value 3.2

Then you want to update above table with data below

DECLARE @NewDataToUpdateExistent TABLE ([Row] int, [Col] int, Val nvarchar(50));
INSERT INTO @NewDataToUpdateExistent([Row], [Col], Val)
VALUES 
(1,10,'Updated Value 1'),
/*(1,11,'Deleted Value 2'),*/
(1,12,'Updated Value 3.1'),
(1,12,'Updated 3.2'),
(1,12,'New Value 3.3')

Row         Col         Val
----------- ----------- --------------------------------------------------
1           10          Updated Value 1
1           12          Updated Value 3.1
1           12          Updated 3.2
1           12          New Value 3.3

I want to do some performant update (without using cursors or other ugly iteration stuff) resulting in the following final data on main table (@ExistentData)

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Updated Value 1
1002        1           12          Updated Value 3.1
1003        1           12          Updated Value 3.2
1004        1           12          New Value 3.3

Deleting all related data and insert again (atm I'm doing this 😅) is not an option cause is an expansive operation regarding FK with other tables and index/statics management.

  • Is there some reason you can't use an ordinary UPDATE? It looks like a simple join on two columns. Have you tested this and found it to be too slow? – Jon of All Trades Aug 2 at 13:42
  • @JonofAllTrades the main problem with ordinary UPDATE is one to many relation. For the same [Row] and [Col] I might have more than one [Val] (e.g Row 1, Col 12). Maybe is simplier than I thought but I'm stuck with this. – Guillermo Subiran Aug 2 at 13:52
  • 1
    Ah, sorry, I didn't notice that Col wasn't unique in the second example. I guess I need more coffee. So by what criteria do you want #1002 and #1003 changed to 3.1 and 3.2 respectively, rather than 3.2 and 3.1, or 3.3? – Jon of All Trades Aug 2 at 14:54
  • @JonofAllTrades no problem! Regarding your question I don't care about that order. Now I'm trying something using ROW_NUMBER in a way as a colleague suggested – Guillermo Subiran Aug 2 at 15:04
1
DECLARE @ExistentData 
TABLE (ID INT IDENTITY(1000,1), [Row] INT, [Col] INT, Val NVARCHAR(50));

INSERT INTO @ExistentData([Row], [Col], Val)
VALUES 
(1,10,'Value 1'),
(1,11,'Value 2'),
(1,12,'Value 3.1'),
(1,12,'Value 3.2')
select * from @ExistentData    

DECLARE @NewDataToUpdateExistent TABLE ([Row] int, [Col] int, Val nvarchar(50));
INSERT INTO @NewDataToUpdateExistent([Row], [Col], Val)
VALUES 
(1,10,'Updated Value 1'),
/*(1,11,'Deleted Value 2'),*/
(1,12,'Updated Value 3.1'),
(1,12,'Updated 3.2'),
(1,12,'New Value 3.3')
select * from @NewDataToUpdateExistent

With ROW_NUMBER you do the trick

DECLARE @A TABLE (ID INT, [Row] INT, [Col] INT, Val NVARCHAR(50), [RowNumb] INT);
INSERT INTO @A (ID, [Row], [Col], Val, RowNumb)
SELECT A.ID, A.[Row], A.Col, A.Val,
ROW_NUMBER() OVER 
    (PARTITION BY A.[Row], A.[Col] ORDER BY A.[Row], A.[Col] DESC) AS RowNumb
FROM @ExistentData A
;

DECLARE @B TABLE ([Row] INT, [Col] INT, Val NVARCHAR(50), [RowNumb] INT);
INSERT INTO @B ([Row], [Col], Val, RowNumb)
SELECT B.[Row], B.Col, B.Val,
ROW_NUMBER() OVER 
    (PARTITION BY B.[Row], B.[Col] ORDER BY B.[Row], B.[Col] DESC) AS RowNumb
FROM @NewDataToUpdateExistent B
;

Now just joining tables in the following way you get the expected result

--Delete
DELETE ed
FROM @A a
LEFT OUTER JOIN @B b 
    ON  a.[Row] = b.[Row] AND a.[Col] = b.[Col] 
    AND a.[RowNumb] = b.[RowNumb]
INNER JOIN @ExistentData ed ON a.ID = ed.ID
WHERE b.[Row] IS NULL

--Update
UPDATE ed
SET ed.Val = B.Val
FROM @A a
INNER JOIN @B b 
    ON  a.[Row] = b.[Row] AND a.[Col] = b.[Col] 
    AND a.[RowNumb] = b.[RowNumb]
INNER JOIN @ExistentData ed ON a.ID = ed.ID

--INSERT
INSERT INTO @ExistentData([Row], [Col], Val)
SELECT b.[Row], b.[Col], b.Val
FROM @B b
LEFT OUTER JOIN @A a 
    ON a.[Row] = b.[Row] AND a.[Col] = b.[Col] 
    AND a.[RowNumb] = b.[RowNumb]
WHERE A.[Row] IS NULL

SELECT * FROM @ExistentData ORDER BY id

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Updated Value 1
1002        1           12          Updated Value 3.1
1003        1           12          Updated Value 3.2
1004        1           12          New Value 3.3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.