1

Trying to wrap my head around this. I am posting data to a SQLite db and would like to insert records if they do not exist, and update existing records if certain requirements are met (basically if one field has changed values). Not sure exactly how to do something like this. I've seen examples using ON DUPLICATE KEY, but that basically updates records when there is a duplicate. I need to also check for a change before updating.

Any ideas?

George Rodriguez
  • 11
  • 2
  • 1
    there's not really any such thing as a "conditional insert". insert queries have no `where` clause, and the `on duplicate key update ...` business is about as close as you could get to making it conditional. – Marc B Apr 21 '16 at 19:19
  • Why do you only need to update if a field has changed values? If you update and it doesn't have changed values won't that just not do anything? – Chris Apr 21 '16 at 19:19
  • @Chris I think he means he only wants to update column B if column A's value is changing. – Barmar Apr 21 '16 at 19:37
  • 1
    @Barmar Okay, well in that case it seems like the neatest way of doing it would be to just use application logic rather than crafting a rather horrible SQL query. – Chris Apr 21 '16 at 19:38
  • SQLite doesn't have `ON DUPLICATE KEY`, see http://stackoverflow.com/questions/23622504/on-duplicate-key-not-working-in-sqlite – Barmar Apr 21 '16 at 19:43
  • there is no MERGE statement in SQLite, but consider [this](http://sqlite.1065341.n5.nabble.com/SQLite-equivalent-to-Oracle-s-MERGE-INTO-td67271.html) and [this](http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace?rq=1) – Cee McSharpface Apr 21 '16 at 20:23

1 Answers1

0

We have two options:

1) "ON CONFLICT" on table creation SQL, for this case you should use "ON CONFLICT REPLACE";

2) "UPSER" on insert SQL, for this case you should put "ON CONFLICT(field) DO UPDATE SET ..."

Jonas WebDev
  • 185
  • 11