CODEWITHSUNDEEP
phpmysqlsql
Mattis
Mattis

Reputation: 5096

SQL query with two conditions, update the one matching

Table:

id fid nfid
1  10  44
2  44  5
3  18  8

Variables:

$oldid = 44;
$newid = 99;

Current query:

UPDATE Table SET fid = $newid WHERE fid = $oldid OR nfid = $oldid

This obviously updates the fid to $newid, but I want it to update fid OR nfid, whichever is matched in the WHERE clause. Is this possible to complete in one query, or do I have to do two updates?

Pseudo SQL:

UPDATE Table SET fid = $newid OR nfid = $newid WHERE fid = $oldid OR nfid = $oldid

Happy for any input. Thanks.

Upvotes: 2

Views: 395

Answers (3)

Legionar
Legionar

Reputation: 7597

You have to use IF, I also added there quotes, to read it better:

mysql_query("
  UPDATE Table
  SET
    fid = IF (fid = ".$oldid.", ".$newid.", fid),
    nfid = IF (nfid = ".$oldid.", ".$newid.", nfid)
";

Explanation:

if fid = $oldid, then it will do fid = $newid, otherwise it will leave value (fid = fid)

if nfid = $oldid, then it will do nfid = $newid, otherwise it will leave value (nfid = nfid)

Upvotes: 2

bhamby
bhamby

Reputation: 15450

Why not just use two separate update statements?

UPDATE Table 
   SET fid = $newid 
 WHERE fid = $oldid
;

UPDATE Table 
   SET nfid = $newid
 WHERE nfid = $oldid
;

Otherwise, you'll have to do some funky stuff with a CASE statement:

UPDATE Table
   SET nfid = CASE WHEN nfid = $oldid THEN $newid ELSE nfid END
      , fid = CASE WHEN  fid = $oldid THEN $newid ELSE  fid END
 WHERE nfid = $oldid
    OR  fid = $oldid

Upvotes: 2

gwillie
gwillie

Reputation: 1899

I think its meant to be written like:

UPDATE Table SET fid = $newid, nfid = $newid WHERE fid = $oldid OR nfid = $oldid

So replace OR with a comma ,

Upvotes: -1

Related Questions