CODEWITHSUNDEEP
sql-servert-sql
TabsNotSpaces
TabsNotSpaces

Reputation: 1357

TSQL: Trying to turn joined select statement into update statement

Not the most complex problem but I've been looking at this one for a while and I'm at a block. Here is a TSQL select statement I want to turn into an update statement. The table structures are included for reference (server, db, and table names simplified from what they actually are).

SELECT m.ConfirmationCode as CodeInMain,
       e.ConfirmationCode as CodeInEvents
FROM Server1.DB1.dbo.MainTable AS m
     INNER JOIN Server2.DB1.dbo.Events AS e ON c.AccountCode = e.AccountCode
                                             AND c.Commodity = ( 
                                                    SELECT Alias
                                                    FROM Server2.DB1.dbo.Commodities
                                                    WHERE pkCommodityId = e.fkCommodityId )
WHERE m.AccountCode = e.AccountCode
  AND m.Brand IN( 'FTR', 'CER' );

Here are the referenced table schemas:

Server1.DB1.dbo.MainTable (ConfirmationCode varchar(100), AccountCode varchar(100), Commodity varchar(1), Brand varchar(3))

Server2.DB1.dbo.Events (ConfirmationCode varchar(100), AccountCode varchar(100), Commodity int)

Server2.DB1.dbo.Commodities (pkCommodityId int, Alias varchar(100))

Server 2 is linked to Server 1.

The current output of the select statement is:

CodeInMain | CodeInEvents
--------------------------
AN235cAc0a |    NULL
CSORSX239c |    NULL
...

All of my outputted information is as expected.

My goal is to update e.ConfirmationCode as CodeInEvents with the data in m.ConfirmationCode as CodeInMain but I am getting stuck on how to accommodate for the join. I realize that a cursor can be used to cycle through the contents of the above output when stored in a temporary table, but I would really like to be able to do this in a single update statement.

Upvotes: 1

Views: 48

Answers (2)

TabsNotSpaces
TabsNotSpaces

Reputation: 1357

got it. 

Update e
set e.ConfirmationCode = m.ConfirmationCode
from Server2.DB1.dbo.Events e
inner join Server1.DB1.dbo.MainTable m on m.AccountCode = e.AccountCode and m.Commodity = (select Alias from Server2.DB1.dbo.Commodities where pkCommodityId = e.fkCommodityId)
where m.AccountCode = e.AccountCode and m.Brand in ('FTR', 'CER')

Upvotes: 0

Code Different
Code Different

Reputation: 93161

SQL Server has a non-ANSI extended UPDATE syntax that supports JOIN:

UPDATE e
SET e.ConfirmationCode = m.ConfirmationCode
FROM Server1.DB1.dbo.MainTable AS m
     INNER JOIN Server2.DB1.dbo.Events AS e ON c.AccountCode = e.AccountCode
                                             AND c.Commodity = ( 
                                                    SELECT Alias
                                                    FROM Server2.DB1.dbo.Commodities
                                                    WHERE pkCommodityId = e.fkCommodityId )
WHERE m.AccountCode = e.AccountCode
  AND m.Brand IN( 'FTR', 'CER' );

Upvotes: 2

Related Questions