sqlite3 bulk update from temporary table (CTE)

Question

I want to update a special column of every row that meets certain criteria.

This two answeres seemed very promising, but sqlite3 (v 3.24.0) throws an error.

Update multiple rows with different values in a single SQL query

How to use CTE's with update/delete on SQLite?

Here is what i do:

WITH tmp(md5, abs_path) AS (VALUES
            ('7dc108663732380b2596ec643f4f9122', '/path1'),
            ('80f81e1ebea9a77a336d5d0b29fe8772', '/path2'),
            /* here will be many more lines later... */
            ('f42f5c59786de8de804bf1c0d2017e95', '/path3')
                )
UPDATE files SET
    md5sum=(
        SELECT md5 FROM tmp
        WHERE 
            files.absolute_path==tmp.abs_path
    )
WHERE
        files.last_seen_ts=1644002082
    AND
        files.volume_id=1111
    AND
        files.inum IN (SELECT inum FROM files WHERE files.absolute_path==tmp.abs_path)
;

the error is: Error: near line 1: no such column: tmp.abs_path

Can somebody help me with that?

Edit: Thanks for asking me to clarify.

The table "files" has the columns

absolute_path, md5sum, inum

and some others. When I insert the values, the md5 sum is not yet generated and there is only a placeholder. This is, because I have files with different paths but same inum (hard links). So to not unnecessarily calculate the same md5 hash multiple times, I exclude multiples of inums before the md5 calculation.

This md5 sums I have to UPDATE now for every entry in the table "files" that has the same inum as that one I calculated the md5 for and that's now in the "tmp" table to bulk update "files". To achieve this I have the WHERE condition to update all rows where the inum is the same as it is in absolute_path that must be the same path as in the tmp table with its md5 sum. So:

files.inum IN (
SELECT inum FROM files 
WHERE files.absolute_path==tmp.abs_path
)

I hope it's more clear now what I want.

You should explain better what you want to do, because this part of your query: `files.inum IN (SELECT inum FROM files WHERE files.absolute_path==tmp.abs_path)` throws the error that you get. — forpas, Feb 05 '22 at 14:31
@forpas thanks for asking. I edited the question and hope it's more clear now. — nkonde, Feb 05 '22 at 17:37
@MatBailie Oh, sorry!! that was a typo... no, it sais ```tmp.abs_path```. I edited this in the question. — nkonde, Feb 05 '22 at 18:41

score 0 · Accepted Answer · answered Feb 05 '22 at 22:08

You should just use UPDATE FROM syntax...

https://www.sqlite.org/lang_update.html#upfrom

Something like...

WITH
    tmp(md5, abs_path)
AS
(
    VALUES
        ('7dc108663732380b2596ec643f4f9122', '/path1'),
        ('80f81e1ebea9a77a336d5d0b29fe8772', '/path2'),
        /* here will be many more lines later... */
        ('f42f5c59786de8de804bf1c0d2017e95', '/path3')
)
UPDATE
    files
SET
    md5sum = tmp.md5
FROM
    tmp
WHERE 
        files.absolute_path = tmp.abs_path
    AND files.last_seen_ts  = 1644002082
    AND files.volume_id     = 1111

Thank you very much! I had an error first but then saw that I had to update my sql first (v 3.33.0 needed, according to your documentation link). Seems to work now! Thanks! :) — nkonde, Feb 06 '22 at 10:37

sqlite3 bulk update from temporary table (CTE)

1 Answers1