sqlite insert or replace to avoid primary key violation


Notice how the second insert fails because one of the primary keys is not unique.  However, if you use insert or replace, the insert command acts somewhat like an update.

sqlite> create table a (a int constraint pk primary key, b int);
sqlite> insert into a values (1,1);
sqlite> insert into a values (1,5),(2,2);
Error: column a is not unique
sqlite> select * from a;
1|1
sqlite> insert into a values (3,5),(2,2);
sqlite> select * From a;
1|1
3|5
2|2
sqlite> insert or replace into a values (1,9),(2,9),(3,9);
sqlite> select * from a;
1|9
2|9
3|9

Comments

Popular Posts