How to use CASE WHEN / THEN / ELSE with IN?

advertisements

Here's my query:

UPDATE tbl_pedidos_produtos SET status = CASE
    WHEN id IN (SELECT produto_id FROM tbl_pedidos_cotacaos_produtos) THEN 4
    ELSE 3 END WHERE pedido_id = ".$produto->itens[$t]->pedido_id

Is this "WHEN id IN (...)" valid? How can I do such a test?


Yes, this is a valid way do an update.

You can do testing with SQLFiddle like this: http://sqlfiddle.com/#!9/67b3a/1 and http://sqlfiddle.com/#!9/5158af/1

create table test (id int, status varchar(10));
insert into test values (1, null);

create table test2 (id int);
insert into test2 values (2);

update test
set status = case when id in (select id from test2) then 4 else 3 end
where id = 1;

select * from test

Result:

| id | status |
|----|--------|
|  1 |      3 |