MySQL更新与子选择 [英] MySQL update with sub select

问题描述

我有一个表，可以称它为table1，其中两列中的数据已被破坏.

I have a table lets call it table1, and the data in two of the columns has been destroyed.

幸运的是，我有该表的旧备份.

Fortunately I have an old backup of the table.

在旧的备份中，有问题的两列的值是正确的，但其余列已过时，因此我不能简单地导入整个转储.

In the old backup the values for the two columns in question are correct but the rest of the columns are outdated so I can't simply import the whole dump.

因此，我将其导入到另一个表中，该表称为table2.每个记录的ID在两个表中都相同.

So instead I've imported it into another table which I'll call table2. Each record has an id which is the same in both tables.

因此，基本上我需要一个查询，该查询将遍历table1中的每个记录，并使用table2中的相应值更新column1和column2.

So basically I need a query that will go through each record in table1 and update column1 and column2 with the corresponding values from table2.

推荐答案

原始表为table1，备份表为table2

Original table is table1 and backup table is table2

UPDATE table1 t1 JOIN table2 t2 ON t1.id = t2.id
SET t1.col1 = t2.col1, t1.col2 = t2.col2, ...

这篇关于MySQL更新与子选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！