I basically want to create a table like this
col1|col2
---------
1 1
1 2
1 3
2 1
3 1
2 2
1 4
where column 2 autoincrements, but its autoincrement values are not tied to the overall table but column 1's value. Is this possible?
I thought I found a duplicate question, but it was for PostgreSQL. Apologies for temporarily marking your question as a duplicate. I've reversed that.
I don't know for certain if this is possible in SQLite in an automated way, but one solution would be to do it in steps:
BEGIN a transaction and INSERT one row the table with a NULL for the col2. This should acquire a RESERVED lock and prevent other concurrent processes from doing the same thing and causing a race condition.
SELECT MAX(col2) FROM mytable WHERE col1 = ? to get the greatest value inserted for the given group so far.
UPDATE mytable SET col2 = ?+1 WHERE col1 = ? AND col2 IS NULL Using the MAX discovered in step 2.
COMMIT to write the changes to the file.
