SQLite: duplicate row in table with a unique column

Question

Given this table:

CREATE TABLE aTable (aCol TEXT UNIQUE, ...);
INSERT INTO aTable VALUES ( 'one', ... );

I need to duplicate rowid 1 and then change it

BEGIN;
INSERT INTO aTable SELECT * FROM aTable WHERE rowid = 1;
UPDATE aTable SET aCol = 'new unique value' WHERE rowid = 1;
COMMIT;

But that violates the UNIQUE constraint.

How can I retrieve the pre-transaction state of rowid 1?

Of course I could do a SELECT before the transaction, and bind its results to a prepared INSERT statement. I'm also aware of the following, but are there are other methods?

CREATE TEMP TABLE aTemp AS SELECT * FROM aTable WHERE rowid = 1;
...
DROP TABLE aTemp;

Updated
More context: aTable is a history table with normal order, later rows are later versions. However one row (rowid 1) is in progress and therefore not in the history. Thus rowid 1 must be updated to have the latest unique value.

Callie J · Accepted Answer · 2012-02-27 20:30:57Z

2

I'm not sure if I'm missing something obvious, but couldn't you simply do something like:

INSERT INTO table (col1, col2, col3)
    SELECT col1, 'New value', col3 FROM other_table WHERE col1 = 1

...which would duplicate the row apart from col2. What you've not mentioned is where the new unique value will come from, so a bit of guidance around that be helpful.

answered Feb 27, 2012 at 20:30

Callie J

31.4k6 gold badges74 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Liam Over a year ago

I need the new unique value to land in rowid 1, and the existing value to land in the new row.

Callie J Over a year ago

If the new row is a duplicate of the original row, does it matter which row gets the new value?

Liam Over a year ago

Yes. In this app, rowid 1 has a special role, where it always has the latest unique value.

Mike Sherrill 'Cat Recall' Over a year ago

"In this app, rowid 1 has a special role . . ." No good can come from that.

Callie J Over a year ago

It almost sounds like you're creating a history table, but in reverse (i.e., later row IDs have older history). Is this the case? If so, then you're better off turning it on it's head so rowid effectively becomes a version number.

|

sneha · Accepted Answer · 2015-11-11 10:02:45Z

1

vlookup can be implemented

organize your data so that the value you look up is to the left of the return value you want to find.

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

answered Nov 11, 2015 at 10:02

sneha

51 silver badge9 bronze badges

Collectives™ on Stack Overflow

SQLite: duplicate row in table with a unique column

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related