8/21/2016

PHP : Update แบบ Join

PHP : Update แบบ Join
Example :
Using IN:
  1. UPDATE users
  2. SET status='gone'
  3. WHERE id IN
  4.       ( SELECT userID
  5.         FROM usersonline
  6.         WHERE timestamp < '$five_ago'
  7.       )


Using JOIN:
  1. UPDATE users
  2.   JOIN usersonline
  3.     ON usersonline.userID = users.id
  4. SET users.status='gone'
  5. WHERE usersonline.timestamp < '$five_ago'
  6.  


Using EXISTS:
  1. UPDATE users
  2. SET status='gone'
  3. WHERE EXISTS  
  4.       ( SELECT *
  5.         FROM usersonline
  6.         WHERE timestamp < '$five_ago'
  7.           AND userID = users.id
  8.       )


เช่น rp_recreatecopy.php
  1. $SQL = "";
  2. $SQL="UPDATE sci_rp.RPQoute";
  3. $SQL.=" JOIN sci_rp.RPLines";
  4. $SQL.=" ON RPLines.RPLineId = RPQoute.RPLineId";
  5. $SQL.=" SET RPQoute.RPID = '".$NewID."'";
  6. $SQL.=" WHERE RPQoute.RPID = '".$RPID."'";
  7. $SQL.=" AND RPLines.Status = 'Confirm'";
  8. $SQL.=" AND RPLines.RPLineId = RPQoute.RPLineId";
  9. $myDB->Update($SQL);

ที่มา http://stackoverflow.com/questions/7988618/php-sql-update-join

No comments:

Post a Comment