嗨,我正试图复制表中的行,但我需要一个不同的id,也可以在该表中。id和second_id都是主键。和外键。
+----+-----------+
| id | second_id |
+----+-----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
+----+-----------+
因此,我需要将id 1的所有second_id复制到id 2,但最终,如果要有id 3,也要复制它。
结果应该是
+----+-----------+
| id | second_id |
+----+-----------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 1 |
| 2 | 3 |
+----+-----------+
id也是一个外键,所以如果我有id 3,也可以像id 2一样复制它有什么解决方案吗?
使用表的自联接来获得id
和second_id
的所有组合。然后使用LEFT JOIN
过滤掉已经存在的组合,这样就可以将其余的插入到表中。
INSERT INTO yourTable (id, second_id)
SELECT DISTINCT t1.id, t2.second_id
FROM yourTable AS t1
CROSS JOIN yourTable AS t2
LEFT JOIN yourTable AS t3 ON t1.id = t3.id AND t2.second_id = t3.second_id
WHERE t3.id IS NULL
演示
您可以简单地使用INSERT IGNORE
来代替LEFT JOIN
。由于(id, second_id)
是主键,因此插入时会忽略重复项。
INSERT IGNORE INTO yourTable (id, second_id)
SELECT DISTINCT t1.id, t2.second_id
FROM yourTable AS t1
CROSS JOIN yourTable AS t2
演示