mysql query更新数据库,使用来自另一个数据库的选择查询更新mysql表

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 have also tried the following query but it does not work either:

UPDATE database1.table1

SET field2 = "SELECT field2 FROM database2.table1"

WHERE database1.table1.field1 = database2.table1.field1

解决方案

UPDATE 1

based on your comment, markup should be part of the join. Here's 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 an 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