CODEWITHSUNDEEP
sqltriggerssqlite
ssokolow
ssokolow

Reputation: 15355

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

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.

Upvotes: 3

Views: 3074

Answers (2)

ssokolow
ssokolow

Reputation: 15355

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.

Upvotes: 0

Robie Basak
Robie Basak

Reputation: 6760

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.

Upvotes: 1

Related Questions