CODEWITHSUNDEEP
mysql
Andris
Andris

Reputation: 1442

mysql SET CASE WHEN THEN with AND not expected result

Initial mysql table

 RowIdPaidusedUnique | TypeOfBookkeepingRecord 
 ---------------------------------------------
     1               |     45
     2               |     45
     3               |     4

Query is 

UPDATE 2_1_paidused 
SET TypeOfBookkeepingRecord = CASE RowIdPaidusedUnique 
WHEN ? THEN ? WHEN ? THEN ? 
AND TypeOfBookkeepingRecord <> ? 
END;

Array is

Array
(
[0] => 1
[1] => 5
[2] => 2
[3] => 5
[4] => 4
)

Want to get

Where RowIdPaidusedUnique is 1 and TypeOfBookkeepingRecord is not 4, then change TypeOfBookkeepingRecord to 5

Where RowIdPaidusedUnique is 2 and TypeOfBookkeepingRecord is not 4, then change TypeOfBookkeepingRecord to 5

But as result got

 RowIdPaidusedUnique | TypeOfBookkeepingRecord 
 ---------------------------------------------
     1               |     5
     2               |     1
     3               |

Seems something is incorrect with AND TypeOfBookkeepingRecord <> ?

Do I need to change to something like 

WHEN ? AND TypeOfBookkeepingRecord <> ? THEN ?

Upvotes: 0

Views: 41

Answers (1)

Rahul
Rahul

Reputation: 77876

You would want to do it like below using CASE statement and put the condition TypeOfBookkeepingRecord is not 4 in WHERE.

See a demo fiddle here http://sqlfiddle.com/#!2/9c831/1 

UPDATE table1
SET TypeOfBookkeepingRecord = 
(CASE WHEN RowIdPaidusedUnique = 1 then 5 
 WHEN  RowIdPaidusedUnique = 2 then 1 END)
WHERE TypeOfBookkeepingRecord != 4

Upvotes: 2

Related Questions