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

The addition of that code indicates an error, what are you CROSS joining to? — JonH, Jun 13 '11 at 19:55
@JohnH Sorry, the cross join where not ment to be there, visual studio adds that automaticly for some reason.. — Evilaid, Jun 13 '11 at 20:01

JonH · Answer 1 · 2022-04-11T00:03:36.647

74

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.

edited Apr 11 '22 at 00:03

answered Jun 13 '11 at 19:43

JonH

32,732
12
87
145

OK, thats about what i tried: Like this: `UPDATE md SET md.status = '3' FROM pd_mounting_details AS md LEFT JOIN pd_order_ecolid AS oe ON md.order_data = oe.id` – Evilaid Jun 13 '11 at 19:47
@Evilaid - I've added this code to the question. It will help to answer it. – Jun 13 '11 at 19:50
@Evailaid as long as order_data is the same type of id from oe than I see no issue. – JonH Jun 13 '11 at 19:51
Should it be pd_mounting_details (underscore added)? Also note that if you are using '3' and not a value from pd_order_ecolid, this is the same as updating without a join. Perhaps you're either looking to use an inner join or a "WHERE oe. IS NOT NULL? – Phil Helmer Jun 13 '11 at 20:06
@JonH I tried to execute the query from visual studio and i get a warning saying: Column or expression 'status' cannot be updated. and after that i get an execution error saying: invalid object name 'md' – Evilaid Jun 13 '11 at 20:07
@Phil Helmer Yes thats right, im using underscore so the name och the table is "pd_mounting_details" The "status" column is of the type tinyint. Now im just tesing but you are right´, im using WHERE so it should look somting like this `UPDATE md SET md.status = 3 FROM pd_mounting_details AS md INNER JOIN pd_order_ecolid AS oe ON md.order_data = oe.idWHERE (oe.id = 98)` – Evilaid Jun 13 '11 at 20:13
Assuming you have a space between "oe.id" and "WHERE", does that run successfully or give an error? – Phil Helmer Jun 13 '11 at 20:18
@Phil Helmer Yes i got the space ;) BUt still it doesnt run! Just the sam error: Column or expression 'status' cannot be updated. And then follows: invalid object name 'md' – Evilaid Jun 13 '11 at 20:22
Strange i cant get it to work but when i try to run the query from SQL server management studio it works! – Evilaid Jun 13 '11 at 20:30
Please add to your original question to include your Visual Studio details (version, project type, the context in which you're running the query/code, etc.) This is not a T-SQL issue. – Phil Helmer Jun 13 '11 at 21:38
4

The outer joined version is pointless because every row will get updated anyway. The inner joined version is unnecessary - a EXISTS subquery achieves the same thing (and unlike the buggy join syntax, EXISTS is standard SQL.) – nvogel Jun 13 '11 at 21:40

score 9 · Answer 2 · answered May 13 '19 at 04:33

9

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';

answered May 13 '19 at 04:33

Csongor Halmai

3,239
29
30

score 9 · Answer 3 · edited Oct 11 '15 at 06:59

9

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)

edited Oct 11 '15 at 06:59

Crazy Coder

784
2
9
24

answered Jun 14 '11 at 12:47

kleinohad

5,800
2
26
34

score 8 · Answer 4 · answered Jan 18 '12 at 23:38

8

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 <> ''

answered Jan 18 '12 at 23:38

Tillito

7,718
7
34
31

score 5 · Answer 5 · answered Jun 14 '11 at 13:04

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.

Update and left outer join statements

5 Answers5

Linked