Refresh mysql table with select query from another database

Question

I have two databases, and I want to update one table with values ​​from another database table. I am using the following query, but it does not work.

UPDATE database1.table1 SET field2 = database2.table1.field2 WHERE database1.table1.field1 = database2.table1.field1 

I also tried the following query, but it doesn't work either:

 UPDATE database1.table1 SET field2 = "SELECT field2 FROM database2.table1" WHERE database1.table1.field1 = database2.table1.field1 

Answer 1

UPDATE 1

based on your comment , markup should be part of the mix. Here is the correct one:

 UPDATE oman.ProductMaster_T INNER JOIN main.ProductMaster_T ON main.ProductMaster_T.ProductID = oman.ProductMaster_T.ProductID SET oman.ProductMaster_T.Markup = main.ProductMaster_T.Markup 

you can even add ALIAS to simplify the statement

 UPDATE oman.ProductMaster_T o INNER JOIN main.ProductMaster_T m ON m.ProductID = o.ProductID SET o.Markup = m.Markup