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; #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.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)