Sql insert if doesnt exist, otherwise (completely different) update

Question

Hi

I want to INSERT a row but if only if it doesnt exist, otherwise i need to increase one column, rewrite the other, etc. How do i do this? Do i need a select? I am hoping there is either an INSERT or UPDATE statement that will let me know if it fails so i can do the other

I am using sqlite ATM and i MAY switch to mysql. Using C#.NET.

Answer 1

I don't know of any pure SQL statement that will do exactly what you want. Most environments for running SQL (e.g. DBI, etc.) have some way to tell you the number of rows affected. So, you could do the update, and if it doesn't hit any rows, then do the insert.

If that isn't an option, do the update first, then the insert. If the row exists, the update will work and the insert will fail on a key violation. If the row doesn't exist, the update will hit no rows and the insert should succeed.

Answer 2

You could also always do both as in:

INSERT IGNORE ...

Then,

UPDATE ...

This will work without an AUTO_INCREMENTING field.

Answer 3

sqlite3_changes will tell you how many rows were affected by the last statement. Try the UPDATE first, if it changes 0 rows do the INSERT.