How to test an SQL Update statement before running it?
What about Transactions? They have the ROLLBACK-Feature.
@see https://dev.mysql.com/doc/refman/5.0/en/commit.html
For example:
START TRANSACTION;SELECT * FROM nicetable WHERE somthing=1;UPDATE nicetable SET nicefield='VALUE' WHERE somthing=1;SELECT * FROM nicetable WHERE somthing=1; #checkCOMMIT;# or if you want to reset changes ROLLBACK;SELECT * FROM nicetable WHERE somthing=1; #should be the old value
Answer on question from @rickozoe below:
In general these lines will not be executed as once. In PHP f.e. you would write something like that (perhaps a little bit cleaner, but wanted to answer quick ;-) ):
$MysqlConnection->query('START TRANSACTION;');$erg = $MysqlConnection->query('UPDATE MyGuests SET lastname='Doe' WHERE id=2;');if($erg) $MysqlConnection->query('COMMIT;');else $MysqlConnection->query('ROLLBACK;');
Another way would be to use MySQL Variables (see https://dev.mysql.com/doc/refman/5.7/en/user-variables.htmlandhttps://stackoverflow.com/a/18499823/1416909):
# do some stuff that should be conditionally rollbacked later onSET @v1 := UPDATE MyGuests SET lastname='Doe' WHERE id=2;IF(v1 < 1) THEN ROLLBACK;ELSE COMMIT;END IF;
But I would suggest to use the language wrappers available in your favorite programming language.
In addition to using a transaction as Imad has said (which should be mandatory anyway) you can also do a sanity check which rows are affected by running a select using the same WHERE clause as the UPDATE.
So if you UPDATE is
UPDATE foo SET bar = 42WHERE col1 = 1 AND col2 = 'foobar';
The following will show you which rows will be updated:
SELECT *FROM fooWHERE col1 = 1 AND col2 = 'foobar';
Autocommit OFF ...
MySQL
set autocommit=0;
It sets the autommit off for the current session.
You execute your statement, see what it has changed, and then rollback if it's wrong or commit if it's what you expected !
EDIT: The benefit of using transactions instead of running select query is that you can check the resulting set easierly.