Copying column data from one table to another in SQLite

Question

I have a table where I need a _id column that grows. However, I cannot use auto-increment due to a table containing 3 primary keys. This is in SQLite, by the way. The table already has data, so I can’t just recreate the table.

Is there a way to insert values ​​from 1 to 148 in the _id column?

We have already tried something like this:

UPDATE table1 SET _id = (SELECT _id FROM table2) 

table2 is a temporary table that contains values ​​from 1 to 148. However, this simply updates all _id values ​​to "1".

Any suggestions on how to solve this?

Answer 1

Not sure what I understand. But let me try.

• table1 has a primary key consisting of three columns.
• One of the columns is called "_id".
• The column "_id" is an integer, but the integer does not automatically increment.
• You want to insert values ​​from 1 to 148 in table1._id.

In SQLite, if either of the other two columns in the primary key is defined as "NOT NULL", you cannot do this. You must simultaneously insert values ​​for all "NOT NULL" primary key components. But if they are invalid, you can insert values ​​into table1._id. Just

 insert into table1 (_id) select _id from table2; 

This will add new rows to table1 with zero values ​​for all columns except _id.