Update referencing on subquery (sqlite)

Question

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 Data before UPDATE

desired Table Data after UPDATE

Give us sample tables and expected result please. – Arnaud Peralta Feb 25 '19 at 10:18 — Arnaud Peralta, Feb 25 '19 at 10:18

forpas · Accepted Answer · 2019-02-27T14:13:55.080

1

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

edited Feb 27 '19 at 14:13

answered Feb 27 '19 at 13:38

forpas

160,666
10
38
76

this doesn't work like expected. The row with file = "abc.txt" and relpath = "/var/tmp" get's an "X" too, but this row exists for the old (123) and the new starttime (234) – Egirus Ornila Feb 28 '19 at 10:50
Did you check the demo? It works for the sample data and the conditions you posted. The only row that gets checked is `def.txt /tmp 123`. – forpas Feb 28 '19 at 11:41
sorry my mistake - my test-db had an error now it works – Egirus Ornila Feb 28 '19 at 15:00

Ajan Balakumaran · Answer 2 · 2019-02-26T05:35:27.523

0

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

edited Feb 26 '19 at 05:35

answered Feb 25 '19 at 10:20

Ajan Balakumaran

1,639
1
8
16

doing this i get a syntax error near . - i think you could not use the alias in the update statement – Egirus Ornila Feb 25 '19 at 13:26
Hi can you try now, I didn't use an alias. – Ajan Balakumaran Feb 26 '19 at 05:36

Update referencing on subquery (sqlite)

2 Answers2