static_rtti static_rtti - 1 year ago 68
MySQL Question

How to test an SQL Update statement before running it?

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 Source

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

  SET bar = 42
WHERE col1 = 1
  AND col2 = 'foobar';

The following will show you which rows will be updated:

FROM foo
WHERE col1 = 1
  AND col2 = 'foobar';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download