CODEWITHSUNDEEP
sqlsqlitesql-updateexists
Egirus Ornila
Egirus Ornila

Reputation: 1314

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

Upvotes: 2

Views: 111

Answers (2)

forpas
forpas

Reputation: 164154

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       |             |

Upvotes: 1

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

I hope this helps you in converting the select statement into an update statement,

UPDATE md5_sums 
SET only_in_old = 'X'

WHERE NOT EXISTS (SELECT *
                  FROM md5_sums newt
                  WHERE file = newt.file
                  and relpath = newt.relpath
                  and newt.starttime = 1551085649.7764235)
and starttime = 1551085580.009046

Upvotes: 0

Related Questions