MySql 使用另一个表中的数据更新一个表

Question

我有两个结构相同的表。 Table1 保存经过审核的数据，table2 保存其余数据。

表1

+------+--------+---------------+--------+-----------+| "id" | "name" | "description" | "type" | "country" |+------+--------+---------------+--------+-----------+| "1"  | "a"    | "x"           | "1"    | "US"      || "2"  | "b"    | "x"           | "1"    | "UK"      |+------+--------+---------------+--------+-----------+

Table 2

+------+-----------+-----------------+--------+-----------+----------+| "id" |  "name"   |  "description"  | "type" | "country" | "status" |+------+-----------+-----------------+--------+-----------+----------+| "1"  | "Title 1" | "Description 1" | "1"    | "US"      | "0"      || "2"  | "Title 2" | "Description 2" | "10"   | "UK"      | "0"      |+------+-----------+-----------------+--------+-----------+----------+

I run the below sql in order to update table 1 with data from table 2, and it works well. The only problem is, I need to specify the id in both places. If I were to specify it only in one place, where would it go?

UPDATE table1 dest, 
       (SELECT name, 
               description 
        FROM   table2 
        WHERE  id = 1) src 
SET    dest.name = src.name, 
       dest.description = src.description 
WHERE  dest.id = 1; 

事情的发展方式是:

UPDATE table1 SET name AND description =
(
   SELECT name, description from table2
   WHERE id=1 AND country=us and type=10
) WHERE id=idfromselect AND country=countryfromselect AND type=typefromselect

我不知道将id和剩余条件放在哪里。你能帮忙吗？

Answer 1

将其作为联接进行，将 id 放入联接条件中，然后仅检查 WHERE 子句中的 id。

类似这样的:-

UPDATE table1 dest INNER JOIN table2 src ON dest.id = src=id
SET dest.name = src.name, dest.description = src.description 
WHERE dest.id=1 ;

任何其他限制都可以添加到 WHERE 子句中