php sql update join

Question

Just learning about sql joins and things, and I have a question.

Can you JOIN on an update query? Or is it only for select ones. Because I have this code;

$five_ago = date("Y-m-d H:i:s", strtotime("$now - 5 mins"));

$sql_result23 = mysql_query("SELECT * FROM usersonline WHERE timestamp < '$five_ago'", $db);
while ($rs23 = mysql_fetch_array($sql_result23)) { 
    mysql_query("UPDATE users SET status='gone' WHERE id ='$rs23[userID]'"); 
}

It picks out from the usersonline table all the ones that are older than 5 minutes, then finds them in the users table and updates their record.

I'm not a JOIN wizard or anything but i think a join would simplify that. Can someone clarify this?

Answer 1

Using IN:

UPDATE users 
SET status='gone' 
WHERE id IN 
      ( SELECT userID 
        FROM usersonline 
        WHERE timestamp < '$five_ago'
      )

Using JOIN:

UPDATE users 
  JOIN usersonline 
    ON usersonline.userID = users.id
SET users.status='gone' 
WHERE usersonline.timestamp < '$five_ago'

Using EXISTS:

UPDATE users 
SET status='gone' 
WHERE EXISTS  
      ( SELECT *
        FROM usersonline 
        WHERE timestamp < '$five_ago'
          AND userID = users.id
      )

---

You could also skip the 5 minutes ago calculations in PHP and let the MySQL engine do that work, with:

WHERE timestamp < NOW() - INTERVAL 5 MINUTE

Answer 2

Yes, you can JOIN in an UPDATE statement, but I would probably use the IN () subquery as suggested elsewhere, as I find the syntax more straightforward than the awkward JOIN below:

UPDATE users 
  JOIN usersonline ON users.id = usersonline.userid
  SET users.status='gone'
WHERE usersonline.timestamp < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

Note also the use of MySQL's own DATE_SUB() so you don't have to handle that in PHP beforehand.