Ctaal Ctaal - 1 year ago 74
SQL Question

SQL server update with 2 left joins and a where clause

I'm trying to update products in my 'products' table that are not present in 2 other tables. The products have a unique identifier 'ean'. My plan was to do 2 left joins with the other tables and then update if both other ean fields contained null. Using MS SQL Server by the way.

Tables: product, table2, table3

update product
set published = 0
from p as product
left join a as table2
on p.ean = a.ean
left join t as table3
on p.ean = t.ean
where t.ean is null and a.ean is null

Since it doesn't seem to work, what am I doing wrong?

Answer Source

I think the problem is with your alias.

If your tables are product, table2, table3 then your query should be:

update P
set published = 0
from product as P
left join table2 as A
  on P.ean = A.ean
left join table3 as T 
  on P.ean = T.ean
where T.ean is null and A.ean is null
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download