update a table using inner join syntax in mysql

Question

i am curious about how updating a table with inner join works. if i run the following statement:

update tbl1 a
inner join tbl2 b using (id)
set a.val = b.val;

what happens to the records in tbl1 that do not have a match in tbl2? will they simply not be updated and left as is in tbl1? will they be deleted?

i realize i can run this and get the answer but i'm also interested in the mechanics of how this works behind the scenes and was hoping somebody could elucidate this for me.

Gordon Linoff · Accepted Answer · 2012-08-15 00:32:47Z

1

The update statement is operating on tbl1.

The join is providing a filter that specifies which rows to update in tbl1.

As an added bonus, the join is also providing a value for the column.

The update statement does not and cannot delete rows from a table.

answered Aug 15, 2012 at 0:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Alex Over a year ago

i guess what i'm trying to understand is what's happening in the background: what if you did left or right join for example? what would mysql set the values to for rows that have no matches? i'm trying to understand the mechanics of what is going on in the background. would it be null, for example, in the left join scenario where tbl2 does not have a matching row?

Gordon Linoff Over a year ago

If tbl2 does not have a matching row, then a.val will be set to NULL.

Alex Over a year ago

then how would an update with an inner join vs. a left join be different? it seems they are the same thing here.

John Woo · Accepted Answer · 2012-08-15 00:30:24Z

1

Q: What happens to the records in tbl1 that do not have a match in tbl2?

A: They will not be updated since they have no match from tbl2. From the definition, the INNER JOIN keyword return rows when there is at least one match in both tables. The only keyword that could delete record from your table is the DELETE DML.

answered Aug 15, 2012 at 0:30

John Woo

265k70 gold badges509 silver badges500 bronze badges

Collectives™ on Stack Overflow

update a table using inner join syntax in mysql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related