Update and left outer join statements

Question

I have two tabels with a relation and I want to update a field in table A. Is it possible to combine update and join in the same query? I googled it but didnt find any working solution?

UPDATE md SET md.status = '3'  FROM pd_mounting_details AS md  LEFT OUTER JOIN pd_order_ecolid AS oe ON md.order_data = oe.id 

I'm using MS SQL

Answer 1

Update t  SET         t.Column1=100 FROM         myTableA t  LEFT JOIN         myTableB t2  ON         t2.ID=t.ID 

Replace myTableA with your table name and replace Column1 with your column name.

After this simply LEFT JOIN to tableB. t in this case is just an alias for myTableA. t2 is an alias for your joined table, in my example that is myTableB. If you don't like using t or t2 use any alias name you prefer - it doesn't matter - I just happen to like using those.

Answer 2

If what you need is UPDATE from SELECT statement you can do something like this:

UPDATE suppliers     SET city = (SELECT customers.city FROM customers  WHERE customers.customer_name = suppliers.supplier_name)