Shmewnix Shmewnix - 9 days ago 6
SQL Question

How to update records in a table from 1 database based on query from another table in a database

I currently have 2 tables

Database 1 total:

lcustomerid sbarcode dtreplicated
NULL 1 NULL
NULL 2 NULL
NULL 3 NULL
1353 4 12/6/15


Database 2 Table :

lcustomerid sbarcode
12353 1
15353 2
53332 3
1353 4


What i'd like to do is make Database 1 Table look like this:

lcustomerid sbarcode dtreplicated
12353 1 NULL
15353 2 NULL
53332 3 NULL
1353 4 12/6/15


If I run this query - I'm able to put together the link between the 2 databases.

SELECT Customers.lCustomerID, Customers.sBarcode, Web.sBarcode AS WebBarcode
FROM Customers RIGHT OUTER JOIN
database1.db1.dbo.customers AS Web ON Web.sBarcode = Customers.sBarcode
WHERE (Web.lCustomerid IS NULL) AND (Web.dtReplicated = '1/1/1900')


How can I update lcustomerid in database 1, based on results from Database 2 where the barcodes match. Based on the query above, i'll be running the query from database 2 (it's setup as a linked server).

Answer

Just use a join:

update c
    set lcustomerid = cc.lcustomerid
    from database1.db1.dbo.customers c join
         Customers  cc
         on cc.barcode = c.barcode
    where lcustomerid is null;
Comments