Update and left outer join statements
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 A开发者_JAVA百科S md
LEFT OUTER JOIN pd_order_ecolid AS oe ON md.order_data = oe.id
I'm using MS SQL
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.
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)
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 <> ''
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';
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.
精彩评论