mysql update + ( where + join) syntax

Question

    $rs = mysql_query("
        SELECT a.id 
        FROM a
        JOIN b ON b.id=a.id
        WHERE 
            b.p1=1 AND 
            a.p1=1");

    while($r = mysql_fetch_assoc($rs))
        $ids[] = $r['id'];

    $rs = mysql_query("
        UPDATE a
        SET p2=2 
        WHERE id IN (".implode(",",$ids).")");

How to do this in one query?

Answer 1

UPDATE a
JOIN   b ON a.id=b.id AND a.p1 = 1 AND b.p1 = 1
SET    a.p2 = 2

Answer 2

You can do it using INNER JOIN which performs better than IN clause:

UPDATE a
       INNER JOIN b
           ON b.id=a.id
              AND b.p1 = 1
              AND a.p1 = 1
SET p2 = 2;

Answer 3

UPDATE a
SET p2=2 
WHERE id IN (SELECT a.id 
        FROM a
        JOIN b ON b.id=a.id
        WHERE 
            b.p1=1 AND 
            a.p1=1)