Search code examples
mysqlsqlphpbb

Setting columns in a table to match highest contextual value in another table using SQL

I’m trying to fix my forum’s botched database with the help of SQL queries in phpMyAdmin. The columns being used are as follows:

  • mybb_posts is a table that stores information for a single Post in each row, while mybb_users is a table that stores information for a single User in each row.
  • mybb_users.uid – The ID of a Forum User
  • mybb_users.lastpost – The Timestamp of the last Post a User made
  • mybb_posts.uid – Refers to which User made a Post
  • mybb_posts.dateline – The Timestamp that appears on a Post

I want set the entry for each user’s lastpost equal to the max value for dateline entries from posts where the uid matches the user’s uid. To express that as best as I can with my limited experience with SQL:

SET mybb_users.uid = MAX(mybb_posts.dateline WHERE mybb_posts.uid = mybb_users.uid)

I’ve given it a few tries, including that shameful display, but all resulted in errors.

Solution

  • I think this should do it:

    UPDATE
  mybb_users,
  (SELECT uid, MAX(dateline) AS date FROM mybb_posts GROUP BY uid) AS lastposts
SET mybb_users.lastpost = lastposts.date
WHERE mybb_users.uid = lastposts.uid

    So what goes on here? The sub query (third line) selects the maximum datelines for every users (thanks to the GROUP BY). The WHERE makes sure the temporary table from the sub query and the mybb_users are matched on the uid.