Is it possible to simulate running a statement in MySQL? For example, say I want to run something like this:
but have MySQL simply spit out a report that shows how many rows would be affected.Code:UPDATE table SET column0='value' WHERE column1='value'
Why not just use a SELECT? That would give you the number of rows that match your WHERE clause...
You may be able to do something with transactions, provided you're running a version of MySQL that supports them, but it'd would be alot more complicated than a simple SELECT....
I'm assuming you don't want to actually execute the UPDATE here? I think there may be a MySQL function to retrieve the number of affected records, but that would assume you actually wanted to execute the update.
I did run the where clause with a select and it gave me the number of rows, but I was just wondering if it was possible to simulate an update... Just paranoia on my part, I guess. I'm always hesitant to run an update that affects some 30k rows... I'm thinking I might want start running two backups a day. Y'know, so I CAN screw everything up and still recover.
I think I see what you mean....So you basically want to know if only the rows you wanted to update were updated?
I think there may be a function in MySQL that returns the number of affected rows...
Try the Handbook documentation MySQL :: MySQL Documentation
I read through some of the MySQL documentation, but couldn't find anything that suggested any kind of simulation execution.
Here's the situation I was encountering:
I have a list of leads and a list of do-not-call phone numbers. I needed to update the leads against the DNC list, setting any matching numbers' status as 'DNC'. First I checked the lists to see how many leads would be affected by the potential update:
My concern came when I ran the actual update. The update statement I needed to use was:Code:SELECT COUNT(*) FROM leads WHERE phone_number IN(SELECT phone_number FROM dnc_list);
However, if I had made a typo, I could have nuked my lead list:Code:UPDATE leads SET status='DNC' WHERE phone_number IN(SELECT phone_number FROM dnc_list);
Ergo, you can see why I was wondering if there was any way to simulate the update. I would see something like, "Query OK, 523455 rows affected" and I would know I made a mistake in my statement without actually committing the update.Code:UPDATE leads SET status='DNC' WHERE phone_number IN(SELECT phone_number FROM leads);
Well, of course there's no way to protect yourself against a typo, but I think the safest way would be to write a little script that assigns your WHERE criteria as a string variable, uses that string in a SELECT COUNT(*)... query, asks you to confirm that you want to continue with the update, then uses the same string variable in an UPDATE... query.
Yes, I am afraid there's very little you can do to protect yourself against typo errors....We've all been there...
I guess there are a couple of options....Use completely different fieldNames in one of your tables, which would throw an error when you tried to run the update query on the wrong table. That way you'd have to have a typo in the tablename and the fieldname to update the wrong table....The other option is to take a copy of each table before you run the update so you've got a backup if it goes wrong....
I can't think of anything that can stop typos....If one update query can cause your whole data audit to fail then I guess I'd be taking a backup before I ran it each time...That's the only real safe answer I guess...
It'd be no different to someone from accounts sending an email out saying
when infact they meant to sayOriginally Posted by Accounts
That wouldn't be good for business!
Depends how good the customers were at paying the biils don..
Posting Permissions 
LinkBack URL
About LinkBacks
Reply With Quote
Bookmarks