Update values in one table from data in another

Question

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.

Answer 1

To answer the first part of the question, I did the following:

Created two tables:

CREATE TABLE master
(
  m_id INTEGER,
  m_value VARCHAR(4)
);

CREATE TABLE scheduled
(
  key INTEGER,
  s_id  INTEGER,
  s_value VARCHAR(4),
  due TIMESTAMP
);

Added some records:

INSERT INTO master VALUES (1, 'Buy');
INSERT INTO master VALUES (2, 'Buy');

INSERT INTO scheduled VALUES (1, 1, 'Sell', '2018-01-01 12:00:00');
INSERT INTO scheduled VALUES (2, 2, 'Sell', '2018-01-01 12:00:01');
INSERT INTO scheduled VALUES (3, 1, 'Buy',  '2019-01-01 12:00:00');

Then I ran the following query:

UPDATE master
SET m_value =
(
  SELECT s_value
  FROM scheduled s
  WHERE s_id = m_id
  AND due <= CURRENT_DATE
);

The result of SELECT * FROM master after the UPDATE:

m_id   m_value
   1      Sell
   2      Sell

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 UPDATEs with JOINs. I'm not sure what the standard says about using these, but here is your example done using a join.

UPDATE master
SET m_value = s.s_value
FROM  master m
JOIN scheduled s ON m.m_id = s.s_id
WHERE s.due <= NOW();

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 support TRANSACTIONs - take a look at the complete answer to your question here. If my solution doesn't help you, please let me know.

Answer 2

You can try using SQLITE Triggers to update records from another table when you are inserting or updating records in a table.

Trigger will update scheduled table if you are updating records in master table