Sharan De Silva Sharan De Silva - 4 years ago 90
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 = > 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 = > 0,true,false)' at line 1

How can I do it in mySQL?

Answer Source

I would suggest writing this using EXISTS:

UPDATE bill b
    SET b.isPaid = (EXISTS (SELECT 1
                            FROM bill_payment p
                            WHERE p.bill_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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download