UPDATE with CASE

[sKunKbad]

I'm trying to evaluation field A and update field A and B accordingly. If A = 0, then it should become 1. If A = 1, then B should become 1. It seems like it should be so simple, but it's not working:

 

UPDATE `the_table` 
SET `A` = CASE
		WHEN `A` = 0
		THEN `A` = 1
		ELSE `A`
	END,
`B` = CASE
		WHEN `A` = 1
		THEN `B` = 1
		ELSE `B`
	END
WHERE `ID` = 1

 

 

[sunfighter]

You have left a number of things unsaid. Since this is in the MySql section I'm guessing you want to update a DB called 'the_table'. And that your using PHP to preform a SWITCH statement. I am also assuming that A and B are columns in that DB.

 

First thing you need to do is assign $A to the value of column A. (Do that your self).

 

Your PHP Code:

switch ($A) 
        case "0":
                UPDATE 'the_table' SET 'A' = 1;
                break;
        case "1":
                UPDATE 'the_table' SET 'B' = 1;
                break;      

Check the manual here:  http://php.net/manual/en/control-structures.switch.php         

[sKunKbad]

Actually, what I needed was if A = 0, then update A to 1. If A was already 1, then update B to 1.

 

This SQL statement ended up working:

 

UPDATE `the_table` 
SET `B` = CASE
		WHEN `A` = 1
		THEN 1
		ELSE `B`
	END,
`A` = CASE
		WHEN `A` = 0
		THEN '1'
		ELSE `A`
	END
WHERE `ID` = 1

 

I guess I didn't really say why it wasn't working, so nobody would have known what I was after. Thanks for your time.

[ignace]

UPDATE the_table SET A = if(A = 1, 1, B), B = if(A = 0, 1, A) WHERE ID = 1