Update database from another using joins?

Question

I am trying to update a table from another database using joins and having a hard time. This is what I am trying to do in pseudo:

UPDATE [Database1].[dbo].[Sessions]
   SET [SpeakerID] = ?STATEMENT1?
 WHERE ?STATEMENT2?

For "Statement1", this would be coming from another database and table that has columns: SessionID and SpeakerID. How can this be achieved?

please post the structure of two tables and how are they related with each other (*linking columns*) — John Woo, Jan 31 '13 at 00:35
possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — Aaron Bertrand, Jan 31 '13 at 00:37

score 2 · Accepted Answer · answered Jan 31 '13 at 00:37

UPDATE  a
SET     a.SpeakerID = b.colName          -- SET valoue here
FROM    Database1.dbo.Sessions a
        INNER JOIN Database2.dbo.Sessions b
            ON a.SessionID = b.SessionID -- assumes that their 
                                         -- relationship column is SessionID, 
                                         -- change it in your original columnName
WHERE   ....

a and b are called alias. They are useful when you have longer source name.

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

UPDATE L
  SET SpeakerID = R.SpeakerID
  FROM dbo.LocalTable AS L
  INNER JOIN RemoteDatabase.dbo.RemoteTable AS R
  ON L.SomeValue = R.SomeValue;

This really is no different from this problem except you have to add a database prefix to one of the tables in the join.

edited May 23 '17 at 11:49

Community

1
1

answered Jan 31 '13 at 00:36

Aaron Bertrand

272,866
37
466
490

score 0 · Answer 3 · answered Jan 31 '13 at 00:44

try

UPDATE [Database1].[dbo].[Sessions]
SET
    Sessions.col1 = other_table.col1
FROM
    [Database1].[dbo].[Sessions] Sessions
INNER JOIN
    [Database2].[dbo].other_table AS other_table
ON
    Sessions.id = other_table.id
WHERE Sessions.id = ??

Note if the database is on another server you will need to create a linked server first

http://msdn.microsoft.com/en-us/library/ff772782.aspx

Update database from another using joins?

3 Answers3