Abayob Abayob - 1 year ago 72
SQL Question

Difference UPDATE on zero rows versus unchanged records?

I just figured out the result of a MySQL query between updating no matched records and updating a matched record that already has the values has no differences?

I have a query like this:

UPDATE uri SET is_active = 1 WHERE f_site_id = '73' AND language_code = 'NL' AND additional = 'item' AND is_forwarder = '0'

If I run this query and there are matched records, but their data does not change, I get a result of 0 rows affected?
If I don't have any records with the above WHERE selection, MySQL returns 0 affected records....

I need to know after the update if there are matched records or not.
It doesn't really matter if the data in the record is already the same, but I need to know that there are matched records.

How do I achieve this?

Answer Source

Just use a separate SELECT query prior to Update:

SELECT count(*) as count FROM uri WHERE f_site_id = '73' AND language_code = 'NL' AND additional = 'item' AND is_forwarder = '0'

Counting rows is not a very resource-intensive task and you do not seem to have many complex conditions.

If you want to optimize queries, then after the SELECT check for count value, and only run UPDATE query if it's >0. No sense in running Update if there's nothing to update.

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