Mysql – Sql insert if doesn’t exist, otherwise (completely different) update


I want to INSERT a row but only if it doesn't exist, otherwise I need to increase one column, rewrite the other, etc. How can 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.

Best Solution

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.