I have an application using SQLite with two tables a master
table and a scheduled
table.
I would like to update a value in the master
table with the value in the scheduled table if I run a query some time after due
has occurred. Once the master
table is updated, I would like to delete those values from the scheduled
table.
Imagine this is the starting state:
Table: master
id value
--- -----
1 "Buy"
2 "Buy"
Table: scheduled
key id value due
---- --- ------ -----
1 1 "Sell" 2018-01-01 12:01:00
2 2 "Sell" 2018-01-01 12:01:00
3 1 "Buy" 2019-02-02 13:00:00
I'm trying to craft a query where if I run it after 2018-01-01 12:01:00
, I would like to see:
Table: master
id value
--- -----
1 "Sell"
2 "Sell"
Table: scheduled
key id value due
---- --- ------ -----
3 1 "Buy" 2019-02-02 13:00:00
I'm new the SQLite, the closest I have gotten is the following:
UPDATE master
SET
value = (SELECT scheduled.value FROM scheduled WHERE scheduled.id = master.id)
WHERE
EXISTS (SELECT * FROM scheduled WHERE scheduled.due >= ?);
Unfortunately this doesn't seem to work, and I'm not even sure where to start on the DELETE
.