Correlated subquery (or equivalent) in an SQLite UPDATE statement?

Question

I have an SQLite3 database which, in order to optimize performance, uses computed columns kept up to date by triggers.

I'm now trying to add a trigger which would be analogous to this (untested but probably valid) SQLAlchemy ORM code

story.read_free = any(link.link_type.read_free for link in story.links)

...but I'm having trouble figuring out how to express that as an UPDATE clause. Here's what I've got so far:

CREATE TRIGGER IF NOT EXISTS update_link_type AFTER UPDATE ON link_types
  FOR EACH ROW WHEN old.read_free <> new.read_free BEGIN
    UPDATE stories SET
      read_free = CASE WHEN (
        SELECT 1 FROM links as l, link_types as lt WHERE lt.id = new.id AND l.link_type_id = lt.id AND l.story_id = stories.id
      ) THEN 1 ELSE 0 END
    WHERE id = (SELECT story_id from links as l, link_types as lt WHERE  l.link_type_id = lt.id AND lt.id = new.id)
  ;
END;

My specific problem is that I can't figure out how to ensure that the subquery in the CASE is correlated.

Either SQLite rejects the syntax (things like UPDATE foo AS bar and UPDATE INNER JOIN ... which are apparently how you do it on other DBs) or, as in the example I gave, it's valid, but has the wrong meaning. (In this case, "Set read_free on this story if there exists any link type with read_free, whether or not the story has links of that type)

If a more clean, concise phrasing of that UPDATE exists beyond simply fixing the problem, I'd also appreciate knowing it. Even if that did work, it'd be a very ugly solution compared to the worst of the rest of my triggers.

What is the schema of the link_types table, and what does the update query look like? Also, which columns are computed? — Jay Godse, Dec 16 '10 at 04:19
The update (aside from not working in its current form) is the large code block in my question. I'm not really sure `link_type`'s schema matters beyond it having a `read_free` column and a primary key named `id` that's referenced by `links`. — ssokolow, Dec 16 '10 at 21:55

score 1 · Answer 1 · answered Dec 16 '10 at 06:32

1

Instead of an UPDATE, could you use a INSERT OR REPLACE instead? Unlike UPDATE, INSERT OR REPLACE will accept an embedded SELECT, so you could do the UPDATE foo AS bar or UPDATE INNER JOIN style thing. Your SELECT would just happen to produce duplicates of the rows in stories with just the columns you need changed.

answered Dec 16 '10 at 06:32

Robie Basak

6,492
2
30
34

Probably not. I'm a big believer in using CHECK constraints and foreign key constraints liberally to prevent duplication. In fact, to ensure that I could, I actually patched the CakePHP SQLite3 data source to emit `PRAGMA foreign_keys=ON` on opening a connection. However, I will think about whether a second table with a schema like `(story_id, read_free)`, a `JOIN` that's always done, a covering index, and `OR REPLACE` would work. – ssokolow Dec 16 '10 at 21:58
I'm not suggesting changing the schema, or actually producing duplicate data. I'm just suggesting using `INSERT OR REPLACE` as a drop-in replacement for `UPDATE` in this particular query. Your database will stay normalised except for the computed columns you already have. When I say duplicates of rows, I mean that the `INSERT OR REPLACE` will effectively always be a `REPLACE` because you've engineered the query to do this, and the embedded `SELECT` will match the existing data, so it will behave the same as the `UPDATE` that you want. – Robie Basak Dec 17 '10 at 00:25
Ahh, point. Give me a bit to experiment with a subquery to preserve/regenerate the three existing computed columns (computed against a different table) and I'll let you know how it turns out. – ssokolow Dec 17 '10 at 16:03

score 0 · Accepted Answer · edited May 23 '17 at 11:55

While composing the INSERT OR REPLACE Robie suggested (Using the REPLACE alias to simplify any potential future port to MySQL), I realized that my mind had been stuck in a rut, making wrong assumptions and overcomplicating the problem. (Probably started working on it while sleep deprived and then never questioned my initial conclusions)

I was then able to reformulate my UPDATE to require only a single JOIN (also not supported by SQLite) and then rewrite that as a WHERE subquery.

Here's the final trigger that resulted:

CREATE TRIGGER IF NOT EXISTS update_link_type AFTER UPDATE ON link_types
FOR EACH ROW WHEN old.read_free <> new.read_free BEGIN
    UPDATE stories SET read_free = new.read_free
        WHERE id IN (SELECT story_id FROM links WHERE link_type_id = new.id)
    ;
END;

Much cleaner and much more maintainable.

I'm awarding the bounty to Robie for two reasons: First, because I'd have never come up with this answer without him jogging me out of that rut. Second, because if my requirements were as I'd originally believed, his answer would be the best.

Correlated subquery (or equivalent) in an SQLite UPDATE statement?

2 Answers2