CODEWITHSUNDEEP
sql
DevEx
DevEx

Reputation: 4571

SQL combine CASE and AND

How to combine an SQL CASE statement with multiple AND, such that I can check for multiple conditions i.e. I want to randomly change region from 233 to 244 for shopid = 455:

select
    id,
    case region
       when 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 1 then 244
       when 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 2 then 233
       else region
    end
from 
    table1

I get this error:

Operand '233' part of predicate '233 AND shopid = 455' should return type 'BOOLEAN' but returns type 'INT'.

FYI FLOOR(RAND()*(3-1)+1) will either yield 1 or 2

Upvotes: 1

Views: 892

Answers (1)

SlimsGhost
SlimsGhost

Reputation: 2909

Use the other form of case:

select
id,
case 
  when region = 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 1 then 244
  when region = 233 and shopid = 455 and FLOOR(RAND()*(3-1)+1) = 2 then 233
  else region
end
from table1

Upvotes: 6

Related Questions