使用来自另一个数据库的选择查询更新mysql表 [英] 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

基于您的评论， markup应该是联接的一部分.这是正确的:

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

您甚至可以添加ALIAS来简化语句，

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

这篇关于使用来自另一个数据库的选择查询更新mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！