sqlite: update TableA with a max() from a joined table
<p>I've been spoiled by years of PostgreSQL and am having a hard time adapting to sqlite's limited <code>update</code> 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).</p>

<p>Scenario:</p>

<p>I have a standard association table setup:  A, A2B, B</p>

<p>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 <code>max</code> function on a B column).</p>

<pre><code>-- 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);
</code></pre>