We help IT Professionals succeed at work.

Need help with a MySQL UPDATE using JOIN

mrh14852
mrh14852 asked
on
I have two tables

Table1
id      Product    Name1
1        ProdA       null
2        ProdB       null
3        ProdC        null
4        ProdD        null

Table2
id      Level    Product
1          1           ProdA
2          2           ProdB
3          2           ProdD

So I need to update Table1.Name1 with Table2.Level which would have this output
id      Product    Name1
1        ProdA         1
2        ProdB          2
3        ProdC        null
4        ProdD          2

This is the select query I run to find which products Name1 has associated with it.
SELECT
Table2.Level,
Table1.Product
FROM
Table1 ,
Table2
WHERE Table2.Product LIKE Table1.Product

So just trying to figure out the UPDATE syntax to update table 1.

Thanks a lot.


Comment
Watch Question

Chief Technology Officer
Most Valuable Expert 2011
Commented:
mrh14852:
Angel Eyes article should give a good explanation for full understanding when you have time for light reading.
http://www.experts-exchange.com/articles/Database/Miscellaneous/UPDATES-with-JOIN-for-everybody.html
For now, the  syntax is:
update table1 t1, table2 t2
set t1.Name1 = t2.level
where t1.Product = t2.Product
;

Author

Commented:
Thanks for the link and thanks for the help, I finally understand that now.

Cheers
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
You are most welcome!
Best regards and happy coding,
Kevin