how to update a value in other table

[tinnu]

Hi guys.... suggestions needed

I have the following tables

Table : Temp3
--------------------------
Temp3ID Char1 Char2
1 x1 NULL
2 x1 312
3 x2 420
4 x2 NULL
5 x3 NULL
6 x3 889


Table : matter
------------------------
MatterID Temp3ID
900 1
901 3
902 4
904 5


Issue: I have to delete Temp3ID's from Temp3 table which are having their Char2 as NULL. i.e (I have to delete
Records from Temp3 table having Temp3ID's {1,4,5} But Before deleting them i have to update the Temp3ID values in the 'matter' table. I have to replace Temp3ID '1' with '2' and Temp3ID '4' with '3' and Temp3ID '5' with '6'

My resultant tables should look like these


Table : Temp3
-----------------------------------
Temp3ID Char1 Char2
2 x1 312
3 x2 420
6 x3 889


Table : matter
--------------------------
MatterID Temp3ID
900 2
901 3
902 3
903 6

I had solved this using CURSORS but due to performance issues i cannot use them. Is there any other way to
handle this.



Thanks in advance!!

[ck9663]

What's your basis on replacing the Temp3ID? I initially thought you want the next available value, but you lost me at "4" will become "3" part...

~~ CK

[MrMob]

UPDATE tableA
SET Colname = tableB.Column FROM tableA , tableB
WHERE tableA.COLID = tableB.COLID

HOpe this will help you as i understand.... Thankyou