Sharan De Silva Sharan De Silva - 3 months ago 6
MySQL Question

Update table based on subquery with if condition

I want to achieve something like this:

update bill b set b.isPaid = (if(select count(*) from bill_payment p where p.bill_id = b.id) > 0,true,false);


but I get this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(*) from res_bill_payment p where p.bill_id = b.id) > 0,true,false)' at line 1

How can I do it in mySQL?

Answer

I would suggest writing this using EXISTS:

UPDATE bill b
    SET b.isPaid = (EXISTS (SELECT 1
                            FROM bill_payment p
                            WHERE p.bill_id = b.id
                           ) 
                   );

The EXISTS clause returns a boolean, so there is no need to explicitly use TRUE and FALSE (unless you really, really want to).

I strongly recommend using EXISTS instead of COUNT(*) for this purpose, because it often has much better performance. EXISTS can stop at the first matching record, instead of having to count all matching records.

Comments