Update-copy data between columns

[frokka]

I need to copy data between columns from two identical tables.
I want to update rows, not add rows, so it's practically a restore.

Table1 contains the columns: aa, bb, cc
and Table2 contains the same columns (where aa is the key.)
There only difference is the Table1 contains more rows.

I'm no expert, but I guess I need to use two cursors for this.
Can someone please help me with the SQL?

 

[PHV]

You may try something like this:
UPDATE Table1 AS A
SET bb=(SELECT bb FROM Table2 WHERE aa=A.aa)
,SET cc=(SELECT cc FROM Table2 WHERE aa=A.aa)
WHERE aa IN (SELECT aa FROM Table2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[frokka]

PHV, thanks for your quick reply, it helped me out

Allthough I should have told you that I'm using MS T-SQL, which apparently differs from ANSI more than I thought. But I only had to tweak the commands minutely to get it working, here is what I ended up with:

UPDATE Table1
SET bb=(SELECT bb FROM Table2 WHERE aa=Table1.aa),
cc=(SELECT cc FROM Table2 WHERE aa=Table1.aa)
WHERE aa in (SELECT aa FROM Table2)


In T-SQl you can only AS-alias columns (with select), not tables.

Cheers!

 

[PHV]

Sorry, I had a severe typo:
UPDATE Table1 AS A
SET bb=(SELECT bb FROM Table2 WHERE aa=A.aa)
,[!]SET[/!] cc=(SELECT cc FROM Table2 WHERE aa=A.aa)
WHERE aa IN (SELECT aa FROM Table2)