How to test an SQL Update statement before running it?

Question

In some cases, running an UPDATE statement in production can save the day. However a borked update can be worse than the initial problem.

Short of using a test database, what are options to tell what an update statement will do before running it?

Answer 1

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; #check  COMMIT; # 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.html and https://stackoverflow.com/a/18499823/1416909 ):

# do some stuff that should be conditionally rollbacked later on  SET @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.

Answer 2

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 = 42 WHERE col1 = 1   AND col2 = 'foobar'; 

The following will show you which rows will be updated:

SELECT * FROM foo WHERE col1 = 1   AND col2 = 'foobar';