update table1, (select ... from table2) subquery
set table1... = subquery...
where table1... = subquery...

mysql> create table phpbb3_users(user_posts int,user_id int);
Query OK, 0 rows affected (0.28 sec)

mysql> insert into phpbb3_users(user_id) select 1 union select 2 union select 3;
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into phpbb3_users(user_id,user_posts) select 4,2;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from phpbb3_users;
+------------+---------+
| user_posts | user_id |
+------------+---------+
|       NULL |       1 |
|       NULL |       2 |
|       NULL |       3 |
|          2 |       4 |
+------------+---------+
4 rows in set (0.00 sec)

mysql> create table phpbb3_posts(poster_id int);
Query OK, 0 rows affected (0.34 sec)

mysql> insert into phpbb3_posts(poster_id) select 1 union all select 2 union all select 2 union all select 3 union all select 3 union all select 3 union all sel
ect 4 union all select 4 union all select 4 union all select 4;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from phpbb3_posts;
+-----------+
| poster_id |
+-----------+
|         1 |
|         2 |
|         2 |
|         3 |
|         3 |
|         3 |
|         4 |
|         4 |
|         4 |
|         4 |
+-----------+
10 rows in set (0.00 sec)

mysql> update phpbb3_users
    -> ,      (
    ->        select count(*) cnt
    -> ,             poster_id
    ->        from phpbb3_posts
    ->        group by poster_id
    ->        ) post_cnt
    -> set phpbb3_users.user_posts=post_cnt.cnt
    -> where phpbb3_users.user_id=post_cnt.poster_id;
Query OK, 4 rows affected (0.09 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from phpbb3_users;
+------------+---------+
| user_posts | user_id |
+------------+---------+
|          1 |       1 |
|          2 |       2 |
|          3 |       3 |
|          4 |       4 |
+------------+---------+
4 rows in set (0.00 sec)

mysql>