CODEWITHSUNDEEP
mysqlsql
Jack_McG
Jack_McG

Reputation: 59

Update Statement using two tables

I need to set the cust in the installed products table to be the ID in the Customers table, have tried a few statements with no luck; just need pointing in the right direction.

Here is what I have:

UPDATE InstalledProducts
    SET InstalledProducts.cust = Customers.ID
FROM InstalledProducts, Customers
WHERE InstalledProducts.SiteName = Customers.SiteName

Any help is greatly appreciated. Thanks

Upvotes: 0

Views: 26

Answers (2)

wvdz
wvdz

Reputation: 16641

Here's how I would write this:

UPDATE InstalledProducts ip
    SET cust = 
(SELECT ID FROM Customers
WHERE ip.SiteName = SiteName)

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

The update statement with JOIN should be as

update 
InstalledProducts ip
join Customers c on c.SiteName = ip.SiteName
set ip.cust = c.ID

Upvotes: 1

Related Questions