Update/Insert to a table in SQL

Question

I have two tables:

TableA

column0 int identity,
Key1 varchar(50),
Key2 varchar(50),
Column3 varchar(50)

TableB

Key1 varchar(50),
Key2 varchar(50),
Column3 varchar(50)

Now I have to insert/update into TableB from TableA depending on the Key1 and Key2 columns. Also not all records in TableA can be considered. Only the records with the MAX identity(column0) value when grouped by key1,key2 need to be considered.

Which RDBMS are you using (MySQL, SQL Server, PostreSQL, ...)? Also, I recommend you read [this article](http://whathaveyoutried.com) — Barranka, Aug 10 '15 at 20:54
I've joined the two tables and updated the records in the target table that need to be updated. I now dont know how to deal with the inserts. Because the records that got updated are still in TableA.And I could delete the records in TableA as I make the updates in TableB. But this would still leave out records in TableA that are lesser than MAX(column0) per group. — that_me123, Aug 10 '15 at 20:55
Please provide a sample of the data and the code you tried and also try to be more specific in the question or you will not get any answer... — Marcellinov, Aug 10 '15 at 21:10

score 0 · Answer 1 · answered Aug 10 '15 at 21:10

Please try the below query:

--update part
update b
set b.Column3=t.Column3
 tblB b inner join
 (select *, row_number() over(partition by Key1,Key2 order by Column0 desc)  as r from tblA) t 
on t.Key1=b.Key1 and t.Key2 =b.key2
where r=1 
--insert part
insert into tblB(Key1,Key2,Column3)
Select t.key1,t.Key2,t.Column3 
from tblB b right join
 (select *, row_number() over(partition by Key1,Key2 order by Column0 desc)  as r from tblA) t 
on t.Key1=b.Key1 and t.Key2 =b.key2
where r=1 and b.Key1 is NULL and b.Key2 is NULL

Update/Insert to a table in SQL

1 Answers1