Update multiple fields from subquery

Question

The current query is:

update topics set posts = (select count(*) from posts where tid = 27), lastpost = (select max(pid) from posts where tid = 27) where tid = 27;

How do I merge the two sub-queries into this for the update query?

select count(*), select max(pid) from posts where tid = 27)

I could execute this as two separate queries obviously (read values from posts, write values to topics) but I'd like to do it in a single query if possible.

Answer 1

You can use an inner join within the update statement :

update topics t
  join (
        select tid, count(*) as cnt, max(pid) as mx
          from posts
         group by tid
       ) p on p.tid = t.tid 
   set posts = p.cnt, lastpost = p.mx
 where t.tid = 27;

Demo

As a side note : In this case not needed but starting from DB version 10.2 window analytic functions might also be used along with aggregation functions.