2

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?

Improve this question
3
  • what is happening when you do "inner join" Commented Feb 14, 2018 at 18:40
  • Add sample data please. Commented Feb 14, 2018 at 18:46
  • @AaronDietz im so sorry I meant even if the UIDs dont differ. Commented Feb 14, 2018 at 18:54

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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?
@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.
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
Improve this answer

Comments