CODEWITHSUNDEEP
sqlite
Liam
Liam

Reputation: 505

SQLite: duplicate row in table with a unique column

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.

Upvotes: 2

Views: 2604

Answers (2)

sneha
sneha

Reputation: 5

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])

Upvotes: 1

Callie J
Callie J

Reputation: 31296

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.

Upvotes: 2

Related Questions