SQL Join and Update query

Question

So I want to update the action column to the value 'Insert' inside Table1, if the ids from Table1 and Table2 match but the UIDs dont.

Right now my query looks like

UPDATE Table1
SET Action = 'Insert'
FROM Table1
    JOIN Table2 ON Table1.id = Table2.id
        AND Table1.UID <> Table2.UID

This is setting the action to Insert even if the UIDs don't differ, can someone help me and explain why this is behaving this way?

@AaronDietz im so sorry I meant even if the UIDs dont differ. — , Feb 14 '18 at 18:54

Aaron Dietz · Accepted Answer · 2018-02-14T19:48:04.790

My assumption is you have something like this:

Table1
id   |  UID  |  action
1    |   1   |   bla
1    |   2   |   bleck
1    |   3   |   floop

Table2
id   |  UID  |  action
1    |   1   |   bla
1    |   2   |   bleck
1    |   4   |   floop

And you hope to update the third row in Table1 because the UID isn't in Table2.

The problem is that the third row in Table2 matches all rows in Table1 on your condition: Table1.id = Table2.id AND Table1.UID <> Table2.UID

Which means that in this case, all rows in Table1 will be updated with Action = 'Insert'

I think you want to use NOT EXISTS():

UPDATE T1
SET Action = 'Insert'
FROM Table1 T1
WHERE NOT EXISTS (SELECT *
                  FROM Table2 T2
                  WHERE T1.id = T2.id
                  AND T1.UID = T2.UID)

Edit, more explanation on why the join fails:

This is a many to many join, meaning that the condition allows multiple rows from Table1 to match multiple rows from Table2

The easiest way to see this in action is to change your update to a select:

SELECT *
FROM Table1 T1
JOIN Table2 T2 on T1.id = T2.id
              and T1.UID <> T2.UID

You may expect this to result in:

id   |  UID  |  action     id   |  UID  |  action
1    |   3   |   floop     1    |   4   |   floop

But really it will result in:

id   |  UID  |  action     id   |  UID  |  action
1    |   1   |   bla       1    |   4   |   floop
1    |   2   |   bleck     1    |   4   |   floop
1    |   3   |   floop     1    |   4   |   floop

This means that when you update you are hitting all the rows for id = 1 in Table1

Thank you so much, the solution worked. Although I'm still a little confused as to why all the rows will update, I thought the join combined and rows where the conditions for each row meet? Why would it change all of them just because one row matches from the other table matches? — , Feb 14 '18 at 19:22
@JoeViscardi NP, I added some further explanation to the answer. Your best bet is to run a select and look at the results - see how the `table1` rows are lining up with the `table2` rows, that should clear it up. — Aaron Dietz, Feb 14 '18 at 19:49

score 0 · Answer 2 · answered Feb 14 '18 at 20:09

0

If you put condition Table1.UID <> Table2.UID into WHERE clause, doesn't it solve your problem?

UPDATE Table1
SET Action = 'Insert'
FROM Table1
    JOIN Table2 ON Table1.id = Table2.id
WHERE Table1.UID <> Table2.UID

answered Feb 14 '18 at 20:09

Karamba

1

SQL Join and Update query

2 Answers2