使用从 1 到 N 的值更新 SQLite 表 [英] Update SQLite table with values from 1 to N

问题描述

我在 SQLite 中有一些表，其中有一列名为 Numbers.该表包含 N 个元素.我想创建某种更新查询，允许我更改 1-N 的 Numbers' 值.

I have some table in SQLite that has a column named Numbers. This table contains N elements. I want to create some kind of an update query that allows me to change Numbers' values for 1-N.

我想添加一个自动递增的新列(所以它完全符合我的意愿).然后将值从我的临时列复制到 Numbers(我不想重命名它以避免处理约束).然后删除临时列.但是...问题是SQLite 不支持ALTER TABLE 的RENAME COLUMN/DELETE COLUMN 之类的东西.

I wanted to add a new column that autoincrements (so it would be exactly as I want to). Then copy values from my temporary column to Numbers (I don't want to rename it to avoid dealing with constraints). Then drop the temp column. But... The problem is SQLite doesn't support anything like RENAME COLUMN/DELETE COLUMN for ALTER TABLE.

如何以这种方式更新我的专栏?

How can I update my column in such a way?

推荐答案

1. 创建临时表；
2. 用主键填充它(如果没有，则使用ROWID)和新计算的Numbers；
3. UPDATE your_table SET Numbers=(SELECT Numbers FROM temp_table WHERE id=your_table.id) WHERE id IN (SELECT id FROM temp_table)
4. 删除临时表.

这篇关于使用从 1 到 N 的值更新 SQLite 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！