mySQL 将行从 2 个表移动到其他表

Question

你好，我似乎无法弄清楚为什么这个 sql 语句不起作用？

我正在尝试将行从表 1 和表 2 移至表 3 和表 4。我认为最简单的方法是选择具有内部联接的行，然后进行 2 INSERT INTO。

BEGIN;
INSERT INTO table3 (id, content, createdBy, createdDate, endTime, startTime) 
VALUES (table1.id, table1.content, table1.createdBy, table1.createdDate, table1.endTime, table1.startTime);
INSERT INTO table4 (table3_Id, sometableid) 
VALUES (table2.table3_Id, table2.sometableid);
SELECT table1.id, 
table1.content, 
table1.createdBy, 
table1.createdDate, 
table1.endTime, 
table1.startTime, 
table2.table2_id, 
table2.sometableid
FROM table1 
INNER JOIN table2 
ON table1.id = table2.table1_id
WHERE table1.endTime < NOW()
COMMIT;

当我运行此 SQL 时，它输出 #1054 - “字段列表”中的未知列“table1.id”

如果有人有任何好的想法，或者能给我指出正确的方向，我将不胜感激!

Answer 1

使用INSERT INTO ... SELECT:

INSERT INTO table3 (id, content, createdBy, createdDate, endTime, startTime)
SELECT id, content, createdBy, createdDate, endTime, startTime
FROM table1
WHERE endTime < NOW();

INSERT INTO table4 (table3_Id, sometableid)
SELECT t2.table2_id, t2.sometableid
FROM table1 AS t1
JOIN table2 AS t2 ON t1.id = t2.table1_id
WHERE t1.endTime < NOW();