Re: executing delete in a case statement?

On Tue, 5 Nov 2002, Jean-Christian Imbeault wrote:

> What is the proper way to execute a delete statement (if possible) in a
> case statement. I am trying various versions of the following but can't

Not really.  Case is a value thing.

> get it to work:
>
> "select member_id, case when (select count(buy_later) from cart_li where
> member_id=cart_id AND buy_later=true) > 0 then (delete from cart_li
> where cart_id=10) end from carts"

BTW: is that really what you want anyway?  It seems like that delete will
delete the cart_li rows for cart 10 no matter which cart you found
buy_later rows for.

If you wanted to delete all rows from cart_li where there existed a
buy_later=true cart_li row for member_id=cart_id (and didn't mind a
somewhat expensive query), I think you could do it in a single delete with
an EXISTS subquery, something like, delete from cart_li where exists
(select 1 from cart, cart_li c2 where cart_li.cart_id=member_id and
c2.cart_id=member_id and c2.buy_later=true);

If it's something more complicated, maybe a plpgsql function.