VALUES ((SELECT id from players WHERE user_name='johnny' AND age=20),įor those who have the latest version of sqlite available, you can still do it in a single statement using INSERT OR REPLACE but beware you need to set all the values. In this way you can have a single SQL command - Table structure Option 2: Insert or replace - keep your own ID Notice: I haven't tested it to see the which triggers are being called, but I assume the following: if row does not exists no point to update user_name to 'johnny' since it's unique, and we filter by it as well Update (will affect row, only if found) WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name='johnny' AND age=20) If you like to avoid both changes()=0 and INSERT OR IGNORE even if you cannot afford deleting the row - You can use this logic įirst, insert (if not exists) and then update by filtering with the unique key. Likewise, you can use Changes() in combination with anything else you want/need anywhere expressions are allowed. The beauty of this is there's no brute-force needed, nor unnecessarily deleting, then re-inserting data which may result in messing up downstream keys in foreign-key relationships.Īdditionally, since it's just a standard Where clause, it can be based on anything you define, not just key violations. If the Update did update an existing row, then Changes() = 1 (or more accurately, not zero if more than one row was updated), so the 'Where' clause in the Insert now evaluates to false and thus no insert will take place. the record doesn't exist) then Changes() = 0 so the Where clause in the Insert statement evaluates to true and a new row is inserted with the specified data. In the above example, if there are no changes from the update (i.e. Changes() represents the number of rows affected by the last operation, which in this case is the update. The 'magic sauce' here is using Changes() in the Where clause. This way works based on any conditions you specify in the update. Here's an approach that doesn't require the brute-force 'ignore' which would only work if there was a key violation. UPDATE players SET age=32 WHERE user_name='steven' Therefore, a improved solution would be: - Try to update any existing row This is not in my opinion a data safety issue, but it is true that firing unnecessary events makes little sense. UPDATE players SET user_name='steven', age=32 WHERE user_name='steven' Īs Andy has commented, trying to insert first and then update may lead to firing triggers more often than expected. INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32) Same data as before: user_name='steven' and age=32. 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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |