Best way to update..?

[kenjoswe]

Hi all,

I have a DTS that loads the data in a temp table and then insert new records into the live table with:

Select distinct * from Table_Temp t1
where not exists(select custnr from Table1 t2 where t1.custno=t2.custno)

What's the easiest way to update fields in the 'not new records'? Is there alternative way other than:

Update Table1 t2
set
t2.Field1=t1.field1,
...
t2.Field99=t1.field99

/Kent J.

 

[bcking]

If T2 holds all of the new data then how about deleting from T1 where a record exists and then insert them from T2.

Steps would be
Delete from T1 where custnr exists in T2
Insert into T1 (Select * from T2)

If T1 joins to other tables using a unique id and not the custnr then you would first have to update T2 and set the unique ID to the same value of T1, then do the delete and insert. (This would also apply if T1 holds other data that T2 doesn't have)

Hope this helps.

Brett