SQL UPDATE statement from SELECT statement?

Question

I have an id and abbreviation column in a table. I would like to update the abbreviation column in another database on another server.. I know how to get a SQL select statement, but how would I get an UPDATE statement from this that to run on the other database?

Basically I want to get something like:

UPDATE table SET abbrv=x WHERE id=1;
UPDATE table SET abbrv=y WHERE id=2;

...

How would I do this?

Answer 1

An easy way to do this is create a SELECT statement to generate the UPDATE statements:

SELECT 
    CONCAT('UPDATE home_provider SET abbrv="', abbrv, '" WHERE id=', id, ';')
FROM home_provider

This will then give you:

UPDATE home_provider SET abrv="ACA" WHERE id=1;
UPDATE home_provider SET abrv="ALL" WHERE id=2;
UPDATE home_provider SET abrv="ARK" WHERE id=3;
UPDATE home_provider SET abrv="ART" WHERE id=4;
...

Answer 2

assuming that there is a trust relationship between the two databases you can do an update with a join:

UPDATE target
SET target.abbrv = source.abbrv
FROM db1.dbo.table1 target
INNER JOIN db2.dbo.table1 source ON target.id = source.id

In the example above db1 would be the target database (where the information is updated) and db2 is the source database (where the data is copied from) and of course table1 would need to be changed to reflect the actual name of the table.

You can also include a where clause if you needed.

UPDATE target
SET target.abbrv = source.abbrv
FROM db1.dbo.table1 target
INNER JOIN db2.dbo.table1 source ON target.id = source.id
WHERE target.id in (1,2,3,5)

If the databases are on separate servers:

UPDATE target
SET target.abbrv = source.abbrv
FROM servara.db1.dbo.table1 target
INNER JOIN serverb.db2.dbo.table1 source ON target.id = source.id