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
.
Best Answer
To answer the first part of the question, I did the following:
Created two tables:
Added some records:
Then I ran the following query:
The result of
SELECT * FROM master
after theUPDATE
:You can find all this on the dbfiddle here. Next time you ask a question, you might find it beneficial to set up a dbfiddle for those who are trying to solve your issue - help us to help you!
BTW, SQLite (unlike other systems) doesn't support
UPDATE
s withJOIN
s. I'm not sure what the standard says about using these, but here is your example done using a join.To answer the second part of the question, do all the work (two separate statements) in one
TRANSACTION
(documentation here, example here). Unfortunately, dbfiddle doesn't appear to support this. BUT, we're in luck, this db-fiddle (note hyphen!) does supportTRANSACTION
s - take a look at the complete answer to your question here. If my solution doesn't help you, please let me know.