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)
desired Table Data after UPDATE
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
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.
| 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 | |