mysql - SQL:将一些字段值复制到同一表内的另一条记录

Question

我有一个表，其中包含大约 100 条用作模板的记录和超过 10000 条反向引用原件的副本记录。我需要从相应的原件中重新初始化副本中的一个字段，但无法弄清楚为什么我不能从子查询中引用外部字段。

示例表 ( SQLFiddle link ):

ID   OriginalID    String
----------------------------
1    NULL          original 1
2    NULL          original 2
3    1             copy of 1
4    2             copy of 2
5    2             another copy of 2

期望的结果:

1 no change
2 no change
3 string should change to 'original 1'
4 string should change to 'original 2'
5 string should change to 'original 2'

我试过的SQL:

update data as target set target.string=(select string from 
(select string from data as source where source.id=target.originalid) as x);

错误:

Unknown column 'target.originalid' in 'where clause'

Answer 1

UPDATE data a
       INNER JOIN data b
          ON a.originalid = b.id
SET a.data = b.data

• > SQLFiddle Demo

输出

╔════╦════════════╦════════════╗
║ ID ║ ORIGINALID ║   STRING   ║
╠════╬════════════╬════════════╣
║  1 ║ (null)     ║ original 1 ║
║  2 ║ (null)     ║ original 2 ║
║  3 ║ 1          ║ original 1 ║
║  4 ║ 2          ║ original 2 ║
║  5 ║ 2          ║ original 2 ║
╚════╩════════════╩════════════╝