SQLite 使用来自内部插入的 id 插入到表中 [英] SQLite insert into table using the id from an inner insert

问题描述

我正在尝试同时插入父项和子项.

I'm trying to insert a parent and child at the same time.

我的想法是插入父级，使用 SELECT last_insert_rowid() AS [Id] 获取 id 并使用此 id 插入子级

My idea is to insert the parent, get the id using SELECT last_insert_rowid() AS [Id] and use this id to insert the child

我可以让每个部分独立工作，但不能作为一个整体.这是我目前拥有的:

I can get each part of this working independently but not as a whole. This is what I currently have:

INSERT INTO ParentTable (Col1) 
VALUES( 'test') 
SELECT last_insert_rowid() AS [Id] 

上述工作 - 到目前为止一切顺利.现在我想在子插入中使用这个结果.这就是我所拥有的:

The above works - so far so good. Now I want to use the result of this in the child insert. This is what I have:

INSERT INTO ChildTable (col1, col2, ParentId) 
VALUES( 1, 2, SELECT Id FROM (
    INSERT INTO ParentTable (Col1) 
    VALUES( 'test') 
    SELECT last_insert_rowid() AS [Id] 
); 

我收到此错误:

靠近SELECT":语法错误:

near "SELECT": syntax error:

有人能指出我正确的方向吗?

Can anyone point me in the right direction?

推荐答案

SELECT 语句中不能使用 INSERT.您应该先插入，然后使用最后插入的 id:

You can't use INSERT in SELECT statement. You should first insert and then use last inserted id:

INSERT INTO ParentTable (Col1) VALUES( 'test');

INSERT INTO ChildTable (col1, col2, ParentId) 
    VALUES(1,2, (SELECT last_insert_rowid()));

由于您想插入许多带有父 ID 的记录，这里有一个解决方法:

Since you want to insert many records with parent ID, here is a workaround:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE IF NOT EXISTS temp(id integer);
DELETE FROM temp;
INSERT INTO ParentTable (Col1) VALUES( 'test');
INSERT INTO temp SELECT last_insert_rowid();
INSERT INTO ChildTable (col1, col2, ParentId) 
    VALUES(1,2, (SELECT id FROM temp LIMIT 1));
.............
COMMIT;
DROP TABLE temp;

或者您可以为此创建一个永久表.

Or you can create a permanent table to this effect.

这篇关于SQLite 使用来自内部插入的 id 插入到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！