CODEWITHSUNDEEP
sql-server
user1296762
user1296762

Reputation: 512

update row if date is more recent

I have a table with 11000 rows (two columns are URN and date) and I want to add rows to it. However if the URN is already there when adding the new rows, it should overwrite the previous record so the date is updated. for example,

select urn, GETDATE() AS Date
into table1
from table2

if urn 10253 has date 23/05/2005 in table1 but the urn is in table2 then it should be replaces with urn 10253 date 10/10/2012

Upvotes: 0

Views: 112

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Here is the syntax using merge, will work from sqlserver 2008:

merge into table1 t1 
using table2 t2 on t1.urn = t2.urn
when not matched then
insert (urn,date)values(t2.urn,t2.date)
when matched then
update
set t1.date = t2.date;

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13486

--Update all the matching records first 

Update t1 SET date=t2.date 
from table1 t1 inner join table2 t2 
on t1.urn=t2.urn

--Insert all the new records

INSERT INTO table1
select * from table2 t1 where NOT EXISTS(select * from table1 t1 where t1.urn=t2.urn)

Upvotes: 1

Related Questions