mysql - 使用临时表中的值更新表中的多行

Question

我正在尝试编写一个数据库迁移脚本，以将一列添加到包含现有数据的表中，然后使用适当的数据填充该列。

我只需几个步骤即可完成迁移。我创建了一个临时表，其中包含一个 ID 如下所示的列:

new_column
==========
1000
1001
1002
1003
...  

我现在想要更新现有表，以便使用上面临时表中的每一行来更新现有表中的每一行。现有的表如下所示:

old_column_1 | old_column_2 | new_column
========================================
1            | 100          | null
2            | 101          | null
3            | 102          | null
...

我尝试了此类更新的一些变体 -

select min(t.new_column) 
from temp t 
where t.new_column not in (select new_column from existing_table);

但我似乎无法正确理解语法......

Answer 1

你的问题比你想象的要复杂。没有什么可靠的可以加入的。因此，要么编写一个存储过程，使用游标循环遍历两个表并逐行更新现有表(这很快就会成为性能噩梦，因此我不推荐它)，要么使用这个有点复杂的查询:

CREATE TABLE temp
    (id int auto_increment primary key, `new_column` int)
;

INSERT INTO temp
    (`new_column`)
VALUES
    (1000),
    (1001),
    (1002),
    (1003)
;



CREATE TABLE existing
    (`old_column_1` int, `old_column_2` int, `new_column` varchar(4))
;

INSERT INTO existing
    (`old_column_1`, `old_column_2`, `new_column`)
VALUES
    (1, 100, NULL),
    (2, 101, NULL),
    (3, 102, NULL)
;

update 
existing e 
inner join (
  select * from (
    select
    t.*
    from temp t
  )t
  inner join
  (
    select
    e.old_column_1, e.old_column_2,
    @rownum := @rownum + 1 as rn
    from existing e
    , (select @rownum:=0) vars
  )e on t.id = e.rn
) sq on sq.old_column_1 = e.old_column_1 and sq.old_column_2 = e.old_column_2
set e.new_column = sq.new_column;

• 看到它在 中实时运行 sqlfiddle

我在您的临时表中添加了一个 auto_increment 列。要么你这样做，要么你像我在这里所做的那样模拟行号:

    select
    e.old_column_1, e.old_column_2,
    @rownum := @rownum + 1 as rn
    from existing e
    , (select @rownum:=0) vars

如果您想影响哪一行获得哪个行号，您可以在其中使用 ORDER BYwhatever_column ASC|DESC 。

因此，查询的基本作用是在现有表中创建行号，并通过此列和临时表中的 auto_increment 列将其连接起来。然后我再次将这个子查询加入到现有表中，这样我们就可以轻松地将临时表中的列复制到现有表中。