Same data as before: user_name='steven' and age=32.
SQLIGHT INSERT IF DOESNT ALREADY EXIST UPDATE
The logic is, for each UPSERT that you want to perform, first execute a INSERT OR IGNORE to make sure there is a row with our user, and then execute an UPDATE query with exactly the same data you tried to insert. No go.Īnd then, finally I used the brute force, with success. And this CASE can't be used (or at least I did not manage it) to perform one UPDATE query if EXISTS(select id from players where user_name='steven'), and INSERT if it didn't. So, I first thought of a IF clause, but SQLite only has CASE. Besides, I created the table with the clause ON DELETE CASCADE, which would mean that it'd delete data silently. Option 2: You cannot afford deleting the rowĪfter monkeying around with the previous solution, I realized that in my case that could end up destroying data, since this ID works as a foreign key for other table. It returns the id of the user 'steven' if any, and otherwise, it returns a new fresh id.
Look at this code: INSERT INTO players (id, name, age)Ĭoalesce((select id from players where user_name='steven'), Let's say we want to UPSERT with the data user_name='steven' and age=32. Now the question comes: what to do to keep the old ID associated? If you are trying to insert/update a player whose ID already exists, the SQLite engine will delete that row and insert the data you are providing. In other words, you don't have foreign key, or if you have them, your SQLite engine is configured so that there no are integrity exceptions.
Option 1: You can afford deleting the row I'd like to share this in a clean format to save some time to the people that may be in my situation. Well, after researching and fighting with the problem for hours, I found out that there are two ways to accomplish this, depending on the structure of your table and if you have foreign keys restrictions activated to maintain integrity.
0 kommentar(er)
