Update a table from identical table

[cbeggan]

Folks,

Can't seem to find this anywhere. I have a table, say Table_A containing rows with UniqueIDs, and another table, say Table B, of identical structure containing more IDs. Some of these are the duplicated in Table_A, some are different. I want to update Table_A from Table_B....

Currently I can easily insert new records that are not in A
INSERT INTO TABLE_A
SELECT *
FROM TABLE_B
WHERE UniqueID NOT IN
(SELECT UniqueID
FROM TABLE_A)

But I can't find a similar way to do my update !

I could use an inner join and explicity
SET T1. Col1 = T2.Col1
...
T1.ColN = T2.ColN
WHERE T1.UniqueID = T2.UniqueID

But that will a) be messy (wide table) and b) require an update to the SQL if the table structures change. I would like something like the INSERT above to update the entire row automatically. Is there a simple SQL syntax for this....

Free Mince Pies for the neatest solutions...!!

------
Dublin, Ireland.

 

[cbirknh]

Try this

Update a
Set a.field1 = b.field1, a.field2 = b.field2, etc
From TABLETOBEUPDATED a inner join tbl2 b on a.UniqueID = b.UniqueID

Also, for the insert, I think this might be more efficient
INSERT INTO TABLE_A
SELECT *
FROM TABLE_B b
WHERE NOT EXists
(SELECT * FROM TABLE_A a where a.UniqueID = b.UniqueID)

I hope this helps,
Chris

 

[cbeggan]

Thanks,

I suspect you are right w.r.t. using NOT EXISTS.

However, I am trying to avoid using explicit column names in the UPDATE SQL....

c. ------
Dublin, Ireland.

 

[tlbroadbent]

You have to name the columns in the update statement. That is the syntax. It is very easy to create queries in SQL Query Analyzer. You can simply drag all the column names from the object browser window.

If you don't want to write the update query, just delete matching rows and then run your insert query. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[cbeggan]

Thanks Terry, while it will require a little pre-processing on 'Table_B' your delete-then-insert strategy just might be a really tidy way to do it !

c. ------
Dublin, Ireland.