Update mysql table with select query from another database

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

1 个解决方案

#1

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,

你甚至可以添加一个ALIAS来简化声明,

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

#1