CODEWITHSUNDEEP
mysqlsql
amu03
amu03

Reputation: 371

Update table from another table: Difference between using inner join and update multiple tables?

I would like to update table 1 column 'num' with the value in table2.

might be really basic.. but I could not find the difference between using inner join and update table x, y where xxxx in this scenario.

let's say I have two tables:

table1 
name | num
name1  1
name2  2
name3  3

table2
name | num
name1  11
name2  22
name4  44

what I want is:

table1:

name | num
name1  11
name2  22
name3  3

I could achieve this by running

Method1:

UPDATE table1, table2
SET table1.num = table2.num
where table1.name = table2.name

and

Method2:

UPDATE table1
inner join table2 on table1.name = table2.name
SET table1.num = table2.num

I get the same result and I was wondering if I can use either one.. or is there any difference between these two queries?? I think I need use method1 to update both table.. but if I want to update only 1 table like in this scenario, does it matter which query I use?

Upvotes: 2

Views: 170

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

The two are functionally equivalent -- and both are non-standard SQL syntax (i.e. MySQL extensions). They should produce the same execution plan as well.

That said, I recommend the version with the JOIN. In my opinion, JOIN is always preferable over commas.

Upvotes: 2

Related Questions