When performing an UPSERT operation in SQLite, a "conflict target" refers to column(s) having a uniqueness constraint that triggers an UPSERT. It is specified in the ON CONFLICT
clause and may be omitted in the last clause of the INSERT
statement.
If the conflict target is omitted in the last INSERT
statement, it will trigger for any uniqueness constraint violation that was not captured by prior ON CONFLICT
clauses.
If the INSERT
operation would violate the conflict target, the UPSERT performs a "DO NOTHING
" or "DO UPDATE
" operation instead of inserting the row. This means that for each row of the INSERT
operation, only the first ON CONFLICT
clause that matches the conflict target is executed. If none of the ON CONFLICT
clauses match the conflict target, the INSERT
operation proceeds normally and inserts the row.
For example:
-- SQLite 3.24.0+
INSERT INTO `users` (`username`, `date_login`)
VALUES ('john', '2023-04-02 00:00:00')
ON CONFLICT (`name`) DO UPDATE SET `date_login` = datetime('now');
In this example, the "username
" column is the conflict target, where:
- If no conflict occurs with other "
username
" values, then a new row is inserted; - If a conflict occurs where a row with the same "
username
" already exists in the table, SQLite will update the "date_login
" value of the conflicting row with the current date/time instead of inserting a new row.
This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.