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

User · Answer

The Left join in this query is pointless:

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

It would update all rows of pd_mounting_details, whether or not a matching row exists in pd_order_ecolid. If you wanted to only update matching rows, it should be an inner join.

If you want to apply some condition based on the join occurring or not, you need to add a WHERE clause and/or a CASE expression in your SET clause.

User · Answer

Just another example where the value of a column from table 1 is inserted into a column in table 2   UPDATE  Address SET     Phone1   sp Phone FROM    Address ad LEFT JOIN Speaker sp ON      sp AddressID   ad ID WHERE   sp Phone  lt  gt

User · Answer

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

User · Answer

In mysql the SET clause needs to come after the JOIN  Example   UPDATE e     LEFT JOIN a ON a id   e aid     SET e id   2     WHERE           e type    user  AND         a country    US

User · Answer

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

[sql-server] Update and left outer join statements

Examples related to sql-server

Examples related to visual-studio

Examples related to tsql