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

User · Answer

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- gt query  START TRANSACTION      erg    MysqlConnection- gt query  UPDATE MyGuests SET lastname  Doe  WHERE id 2     if  erg       MysqlConnection- gt query  COMMIT     else      MysqlConnection- gt 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  lt  1  THEN     ROLLBACK  ELSE     COMMIT  END IF    But I would suggest to use the language wrappers available in your favorite programming language

User · Answer

One more option is to ask MySQL for the query plan  This tells you two things   Whether there are any syntax errors in the query  if so the query plan command itself will fail How MySQL is planning to execute the query  e g  what indexes it will use  In MySQL and most SQL databases the query plan command is describe  so you would do  describe update

User · Answer

In these cases that you want to test  it s a good idea to focus on only current column values and soon-to-be-updated column values   Please take a look at the following code that I ve written to update WHMCS prices     UPDATE tblinvoiceitems AS ii  SELECT                             JUST     ii amount AS old value         FOR     h amount AS new value          TESTING FROM tblinvoiceitems AS ii         PURPOSES   JOIN tblhosting AS h ON ii relid   h id JOIN tblinvoices AS i ON ii invoiceid   i id  WHERE ii amount  lt  gt  h amount       Show only updatable rows    SET ii amount   h amount   This way we clearly compare already existing values versus new values

User · Answer

make a SELECT of it   like if you got  UPDATE users SET id 0 WHERE name  jan   convert it to  SELECT   FROM users WHERE name  jan

User · Answer

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

User · Answer

Not a direct answer  but I ve seen many borked prod data situations that could have been avoided by typing the WHERE clause first  Sometimes a WHERE 1   0 can help with putting a working statement together safely too  And looking at an estimated execution plan  which will estimate rows affected  can be useful  Beyond that  in a transaction that you roll back as others have said

User · Answer

Run select query on same table with all where conditions you are applying in update query

User · Answer

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

User · Answer

I know this is a repeat of other answers  but it has some emotional support to take the extra step for testing update  D  For testing update  hash   is your friend   If you have an update statement like   UPDATE  wp history SET history by  admin  WHERE history ip LIKE  123     You hash UPDATE and SET out for testing  then hash them back in   SELECT   FROM  UPDATE wp history  SET history by  admin  WHERE history ip LIKE  123     It works for simple statements   An additional practically mandatory solution is  to get a copy  backup duplicate   whenever using update on a production table   Phpmyadmin   operations   copy  table yearmonthday  It just takes a few seconds for tables  lt  100M

[mysql] How to test an SQL Update statement before running it?

Answer on question from @rickozoe below:

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to testing

Examples related to myisam