CODEWITHSUNDEEP
sqlite
Jonathan Vanasco
Jonathan Vanasco

Reputation: 15680

sqlite: update TableA with a max() from a joined table

I've been spoiled by years of PostgreSQL and am having a hard time adapting to sqlite's limited update syntax. I cobbled together a working query, but I'm hoping someone might know of a better way. (This isn't very pretty to look at and is the product of sqlite guiding me with error messages).

Scenario:

I have a standard association table setup: A, A2B, B

I want to update every record in A with the "latest" corresponding B (B is joined through the A2B association table, and the "latest" is a max function on a B column).

-- works
UPDATE t_a
SET id_latest_b = (
    SELECT id_b FROM (
        SELECT t_b.id as id_b, 
               max(t_b.timestamp_event),
               t_a2b.id_a
               FROM t_b
               JOIN t_a2b
                   ON (t_b.id = t_a2b.id_b)
               GROUP BY t_a2b.id_a
    ) q_inner
    WHERE 
    t_a.id = q_inner.id_a
);

-- test data
CREATE TABLE t_b (id INT PRIMARY KEY, timestamp_event INT);
CREATE TABLE t_a (id INT PRIMARY KEY, id_latest_b INT REFERENCES t_b(id));
CREATE TABLE t_a2b (id_a REFERENCES t_a(id), id_b REFERENCES t_b(id), PRIMARY KEY (id_a, id_b));
INSERT INTO t_a VALUES (1, NULL);
INSERT INTO t_a VALUES (2, NULL);
INSERT INTO t_a VALUES (3, NULL);
INSERT INTO t_a VALUES (4, NULL);
INSERT INTO t_b VALUES (1, 10);
INSERT INTO t_b VALUES (2, 20);
INSERT INTO t_b VALUES (3, 30);
INSERT INTO t_a2b VALUES (1, 1);
INSERT INTO t_a2b VALUES (1, 2);
INSERT INTO t_a2b VALUES (2, 2);
INSERT INTO t_a2b VALUES (3, 1);
INSERT INTO t_a2b VALUES (3, 2);
INSERT INTO t_a2b VALUES (3, 3);
INSERT INTO t_a2b VALUES (4, 1);
INSERT INTO t_a2b VALUES (4, 4);

Upvotes: 1

Views: 83

Answers (1)

CL.
CL.

Reputation: 180192

You can use ORDER BY to get the row with the largest value:

UPDATE t_a
SET id_latest_b = (SELECT t_b.id
                   FROM t_b
                   JOIN t_a2b ON t_b.id = t_a2b.id_b
                   WHERE t_a2b.id_a = t_a.id
                   ORDER BY t_b.timestamp_event DESC
                   LIMIT 1);

Upvotes: 1

Related Questions