如何将新的表行插入现有表中的每隔一行？
好的，我有一个 sqlite 数据库，大约有 100 行。我正在尝试做的事情有点奇怪，但我需要在每个现有行之间插入一个新行。
我一直在尝试使用 Insert 语句，如下所示，但没有任何运气：
insert into t1(column1) values("hello") where id%2 == 0
所以我基本上尝试使用 % 运算符来告诉我 id 是偶数还是奇数。对于每个偶数 ID 号，我想插入一个新行。
我缺少什么？我可以做些什么不同的事情？如何将新行插入到每隔一行并更新索引？
谢谢
Ok I have a sqlite db, that has roughly 100 rows. It is kind of a strange thing that I'm trying to do, but I need to insert a new row between each of the existing rows.
I have been trying to use the Insert statement as follows, but haven't had any luck:
insert into t1(column1) values("hello") where id%2 == 0
So I'm basically trying to use the %-operator to tell me if the id is even or odd. For every even id number, I'd like to insert a new row.
What am I missing? What can I do differently? How can I insert a new row into every other row and have the index updated as well?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的问题假设这些行具有某种内置顺序，并且您可以在其他行之间插入行。那不是真的。
确实，行在磁盘上有顺序，并且
id列通常按顺序分配，但这是一个实现细节。当您执行查询时，数据库可以按其选择的任何顺序返回行，除非您使用ORDER BY子句指定所需的顺序。现在，我假设您真正想要的是按
id顺序在现有行之间插入行。获得您想要的内容的一种方法如下所示：UPDATE 会将所有现有行的 id 加倍（因此 1,2,3 变为 2,4,6）；那么
INSERT将对 t1 执行查询，并使用结果插入一组新的行，其id值比现有行大一个（因此 2,4,6变为 3,5,7)。我还没有测试上述语句，所以我不知道它们是否有效，或者是否需要一些额外的技巧（如临时表），因为我们在一个语句中查询和更新同一张表。我也可能犯了语法错误。
Your question assumes that the rows have some kind of built-in order to them, and that you can insert rows between other rows. That's not true.
It is true that rows have an order on disk, and that the
idcolumn is usually assigned in order, but that's an implementation detail. When you perform a query, the database is free to return the rows in any order it chooses, unless you specify what you want with anORDER BYclause.Now, I'm assuming what you really want is to insert rows between the existing rows in
idorder. One way to get what you want would look like this:The
UPDATEwould double the ids of all the existing rows (so 1,2,3 becomes 2,4,6); then theINSERTwould perform a query on t1 and use the result to insert a new set of rows withidvalues one more than the existing rows (so 2,4,6 becomes 3,5,7).I haven't tested the above statements, so I don't know if they would work or if they require some extra trickery (like a temporary table) since we are querying and updating the same table in one statement. Also I may have made a syntax error.
不要将这些行视为在数据库中预先排序的。数据库将在它们进入时或根据索引存储它们。您的任务是根据您的需要在检索时（即当您查询数据时）对它们进行排序。
Don't consider the rows as pre-ordered in the database. A database will store them as they come in, or according to an index. It's your task to order them on retrieval (i.e. when you query for data) according to your needs.