Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!
Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.
Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!
I ran into a little "problem" where I wanted to updated multiple rows, in SQLite, each with their own "set" of values.
I also wanted to avoid running an update query for each row.
So how can this be done in SQLite?
Note: this might require a recent SQLite version - I'm guessing 3.0 or newer, since we will use the WITH statement.
Say I have a table:
MyFiles:
ID (PK), Filename, FileSize, FileDate
And I want to update several filenames to a new filename, for example based on the fact if a file actually did get renamed - so it will not be an update of all rows.
We can write this in one single query as follows.
We use the "WITH" statement, and with that we have a pseudo table "Tmp" with values that we add into the query.
The example below will try to find the ID's mentioned in the Tmp "table", and update the "MyFiles" rows that match to the new filename.
WITH Tmp(id, newFilename) AS
(VALUES
(1, 'newfilename.txt'),
(2, 'tweaking4all.txt'),
(3, 'someothername.txt')
...
(13, 'anothernewfilename.txt') ) UPDATE "MyFiles" SET "Filename" = (SELECT newFilename
FROM Tmp
WHERE "MyFiles"."ID" = Tmp.id)
WHERE "MyFiles"."ID" IN (SELECT id FROM Tmp);
