โปรพอประมาณ: PHP : Update แบบ Join

8/21/2016

PHP : Update แบบ Join

PHP : Update แบบ Join
Example :
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
      )

เช่น rp_recreatecopy.php

$SQL = "";
$SQL="UPDATE sci_rp.RPQoute";
$SQL.=" JOIN sci_rp.RPLines";
$SQL.=" ON RPLines.RPLineId = RPQoute.RPLineId";
$SQL.=" SET RPQoute.RPID = '".$NewID."'";
$SQL.=" WHERE RPQoute.RPID = '".$RPID."'";
$SQL.=" AND RPLines.Status = 'Confirm'";
$SQL.=" AND RPLines.RPLineId = RPQoute.RPLineId";
$myDB->Update($SQL);