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 Comments
Leave a Reply. |