SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)

by 
INSERT OR IGNORE INTO visits VALUES ($ip, 0);
UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip;

This requires the “ip” column to have a UNIQUE (or PRIMARY KEY) constraint.

EDIT: Another great solution: https://stackoverflow.com/a/4330694/89771.

More Related Contents:

  1. SQLite – ORDER BY RAND()
  2. Solutions for INSERT OR UPDATE on SQL Server
  3. MySQL query finding values in a comma separated string
  4. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  5. How can I list the tables in a SQLite database file that was opened with ATTACH?
  6. In MySQL queries, why use join instead of where?
  7. Can table columns with a Foreign Key be NULL?
  8. Deleting duplicate rows from sqlite database
  9. SQLite – How do you join tables from different databases?
  10. MySQL not using indexes with WHERE IN clause?
  11. MySQL dump by query
  12. Does SQLite3 not support foreign key constraints?
  13. How many rows in a database are TOO MANY?
  14. SQL – How to transpose?
  15. How to get a list of column names on Sqlite3 database?
  16. Which SQL query is better, MATCH AGAINST or LIKE?
  17. MySQL equivalent of DECODE function in Oracle
  18. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  19. Does DB2 have an “insert or update” statement?
  20. Best database field type for a URL
  21. Query that ignore the spaces
  22. MySQL – how to front pad zip code with “0”?
  23. Android multiple databases open
  24. sqlite select with condition on date
  25. SQLite3 UNIQUE constraint failed error
  26. Find rows that have the same value on a column in MySQL
  27. what is the difference between GROUP BY and ORDER BY in sql
  28. How to find all IDs of children recursively?
  29. UPSERT *not* INSERT or REPLACE
  30. Copy table structure to new table in sqlite3

Leave a Comment