1

I've always used IF() in a a SELECT query as :

  SELECT
    IF(p.A=value,p.B,p.C) AS photo
FROM prof p
WHERE...

But when I use UPDATE and IF() like this SQL seems not to work, is it normal to can't use If() in an UPDATE Query ?

UPDATE table
SET IF(p.A=value,p.B,p.C) = value2
WHERE...
tuze
  • 1,978
  • 2
  • 15
  • 19
Anon
  • 1,201
  • 3
  • 10
  • 14

4 Answers4

2

I think you can re-work the logic to this

p.B = if(p.A=$value, $value2, p.B), // set B=$value2 is p.A=$value
p.C = if(p.A=$value, p.C, $value2)  // set C=$value2 where p.A<>$value
ajreal
  • 46,720
  • 11
  • 89
  • 119
0

You can't assign the results of the IF expression since it isn't directly stored in a table. The update statement expects the left-hand-side of the set expressions to be column names.

Chris
  • 22,923
  • 4
  • 56
  • 50
0

You need to use CASE....WHEN...THEN like this

UPDATE table

SET p.A = ( CASE WHEN (p.A==value) THEN value2 ELSE value3 END ) WHERE ......

OR you need to go here

Community
  • 1
  • 1
Adi
  • 4,766
  • 3
  • 20
  • 22
-1
UPDATE 
    table
SET
    value2 = (SELECT IF(p.A=value,p.B,p.C) AS photo FROM prof p WHERE table.id=...)

Only MySQL 4.1+

Peter
  • 16,453
  • 8
  • 51
  • 77