如何在 SQL 脚本中访问最后插入的行 ID？

发布于 2024-08-13 04:33:03 字数 911 浏览 9 评论 0原文

我正在使用 SQLite，并且有一个属性表和一个子属性表。每个子属性都使用 fkPropertyId 列指向其父属性。现在，为了创建初始数据库，我有一个如下所示的脚本：

INSERT INTO property VALUES(1,.....);
INSERT INTO property VALUES(2,.....);
INSERT INTO property VALUES(3,.....);
   INSERT INTO subproperty VALUES(1,.....,3);
   INSERT INTO subproperty VALUES(2,.....,3);
   INSERT INTO subproperty VALUES(3,.....,3);
INSERT INTO property VALUES(4,.....);

现在，我想摆脱硬编码的 rowId，所以它会是这样的：

INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);
   INSERT INTO subproperty VALUES(NULL,.....,X);
   INSERT INTO subproperty VALUES(NULL,.....,X);
   INSERT INTO subproperty VALUES(NULL,.....,X);
INSERT INTO property VALUES(NULL,.....);

其中 x 指的是最后插入的 rowId在属性表中。现在，有

(SELECT MAX(rowId) FROM property)

没有更好的（技术上更准确的）方法来编写这个脚本？

原文

I'm using SQLite, and I have a table for properties, and a table for sub-properties. Each sub-property points to its parent using the fkPropertyId column. Right now, to create the initial database, I've got a script that looks something like this:

INSERT INTO property VALUES(1,.....);
INSERT INTO property VALUES(2,.....);
INSERT INTO property VALUES(3,.....);
   INSERT INTO subproperty VALUES(1,.....,3);
   INSERT INTO subproperty VALUES(2,.....,3);
   INSERT INTO subproperty VALUES(3,.....,3);
INSERT INTO property VALUES(4,.....);

Now, I want to get rid of the hard-coded rowId, so it would be something like:

INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);
   INSERT INTO subproperty VALUES(NULL,.....,X);
   INSERT INTO subproperty VALUES(NULL,.....,X);
   INSERT INTO subproperty VALUES(NULL,.....,X);
INSERT INTO property VALUES(NULL,.....);

Where x refers to the last inserted rowId in the property table. Right now, that's

(SELECT MAX(rowId) FROM property)

Is there any better (and more technically accurate) way to write this script?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨离汐 2024-08-20 04:33:03

使用 last_insert_rowid 函数：

SELECT last_insert_rowid();

Use the last_insert_rowid function:

SELECT last_insert_rowid();

回复收藏 0 原文

只涨不跌 2024-08-20 04:33:03

好吧，我想出的解决方案使用了 Ben S 的 last_insert_rowid 函数：

INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);

   INSERT INTO subproperty VALUES(1,.....,-1);
   INSERT INTO subproperty VALUES(2,.....,-1);
   INSERT INTO subproperty VALUES(3,.....,-1);
INSERT INTO property VALUES(NULL,.....);
UPDATE subproperty SET fkPropertyId = (SELECT last_insert_rowid()) WHERE fkPropertyId=-1;

INSERT INTO property VALUES(NULL,.....);

不确定这是否是最好的方法，但它对我有用，并且它不使用任何额外的表来存储临时数据。

Well, the solution I came up with used the last_insert_rowid function from Ben S:

INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);

   INSERT INTO subproperty VALUES(1,.....,-1);
   INSERT INTO subproperty VALUES(2,.....,-1);
   INSERT INTO subproperty VALUES(3,.....,-1);
INSERT INTO property VALUES(NULL,.....);
UPDATE subproperty SET fkPropertyId = (SELECT last_insert_rowid()) WHERE fkPropertyId=-1;

INSERT INTO property VALUES(NULL,.....);

Not sure if that's the best approach, but it works for me, and it doesn't use any extra tables for temporary data storage.

回复收藏 0 原文

~没有更多了~