Skip to content
Advertisement

Update referencing on subquery (sqlite)

I have a table with md5 sums for files and use the following query to find the files which exist in one hashing-run and not in the other (oldt vs newt):

SELECT *
FROM md5_sums as oldt
WHERE NOT EXISTS (SELECT *
                  FROM md5_sums as newt
                  WHERE oldt.file = newt.file
                  and oldt.relpath = newt.relpath
                  and newt.starttime = 234)
and oldt.starttime = 123

now i want to put a flag in an extra column with an update clause, like

update md5_sums
set only_in_old = 'X'
where

and there i want a reference to the upper query as subquery, but i cannot find a proper way. Is there a possibility to use the results from the upper query for the where clause from the update-query? (I added now some Table Screenshots with simple Table Data)

Table Description Table Description

Table Data before UPDATE Table Data before UPDATE

desired Table Data after UPDATE desired Table Data after UPDATE

Advertisement

Answer

SQLite does not support aliasing the updated table.
In your case you don’t need that.
You can use the table’s name md5_sums inside the subquery since you aliased the table of the SELECT statement as newt.

UPDATE md5_sums 
SET only_in_old = 'X'
WHERE NOT EXISTS (
  SELECT 1 FROM md5_sums AS newt
  WHERE md5_sums.file = newt.file
  AND md5_sums.relpath = newt.relpath
  AND newt.starttime = 234
)
AND starttime = 123

See the demo.
Results:

| file    | relpath  | starttime | only_in_old |
| ------- | -------- | --------- | ----------- |
| abc.txt | /var/tmp | 123       |             |
| abc.txt | /var/tmp | 234       |             |
| def.txt | /tmp     | 123       | X           |
| xyz.txt | /tmp     | 234       |             |
Advertisement