CODEWITHSUNDEEP
mysqlsqljoinsql-updateinner-join
Alex
Alex

Reputation: 19803

update a table using inner join syntax in mysql

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.

Upvotes: 0

Views: 756

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

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.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

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.

Upvotes: 1

Related Questions