Jun 2, 2022 by Robert Gravelle

In some cases, running a well crafted UPDATE statement in production can save the day. Other times, a botched UPDATE can cause more harm than the initial issue. You can always execute your Data Manipulation Language (DML) statements on a development or test database, but due to differences in the data, this approach makes determining the statement's effects on the production data a craps shoot at best.

So what are some options to accurately predict what the result of an INSERT, UPDATE, or DELETE statement will be on production data before running it? Well, that depends on the database vendor and product, at least in part. There are also some solutions that enjoy widespread support. We'll be taking a look at both options in this blog.

Syntax Check

The process of testing your statements can be split into two stages. The first is to verify that the statement is syntactically valid, I.E. it will execute. The next step is to ascertain whether or not it produces the result that you intended.

One way to validate your syntax is to ask your database (DB) 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 the DB is planning to execute the query, e.g. what indexes it will use.

In most relational DBs the query plan command is "explain" or "describe", as in:

explain update ...;

In Navicat's database administration and development tools, you can run the EXPLAIN command with the click of a button. If the statement fails, you'll get an error message similar to the following:

Otherwise, the query plan will be displayed in a tabular format:

Statement Testing

You can parse a statement to see if it's syntactically valid, but that doesn't mean it will produce the correct results. To see what your query will actually do, you've got a few options.

Turn Off Autocommit

Most relational DBs provide a way to disable autocommit mode so that you must issue the COMMIT statement to store your changes to disk or ROLLBACK to ignore the changes.

In MySQL the command to disable autocommit mode is:

SET autocommit=0

Or

SET autocommit = OFF

In SQL Server, the command is:

SET IMPLICIT_TRANSACTIONS OFF

With autocommit turned off, you are now ready to give your statement(s) a try, by running it within a transaction:

-- 1. start a new transaction
START TRANSACTION;

-- 2. insert a new order for customer 145
INSERT INTO orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
VALUES(@orderNumber,
       '2005-05-31',
       '2005-06-10',
       '2005-06-11',
       'In Process',
        145);
        
-- 3. then, after evaluating the results,
--    rollback the changes
ROLLBACK;

That will leave your DB in exactly the same state as it was before you ran your statement.

Convert Your Statement Into a SELECT

A decidedly low tech approach to testing DML statements is to convert them to SELECTs. As long as you don't expect them to retrieve the entire database, running them as SELECTs is a good way to see exactly which records will be affected. All you need to do is replace the action word with SELECT:

INSERT INTO orders...

BECOMES

SELECT * FROM ORDERS...

Conclusion

There are few things scarier than executing DML statements in a production environment. Thankfully, there are ways to minimize the risk so that you don't have to cross your fingers or recite the Hail Mary.

If you'd like to give Navicat 16 a test drive, you can download a 14 day trial here.