Write better DML queries using SELECT

I’m not sure when I first noticed this trick, but I think its use may have come to me after a session of working with Access.  In the query designer in Access you can click a button that transforms your query from Insert to Update and back to Select.  As a rookie sql server user I’m sure I made a few errant deletes/updates/inserts.  Soon after I learned to start ALL of my DML (Data Manipulation Language) queries, which include Inserts, Updates and Deletes, with a Select statement.  This allows you to see EXACTLY what data you are going to delete, insert or update before you actually do it.  I have caught many mistakes while crafting a SELECT statement before a DML statement that would have caused some headaches later if I had just jumped right into the DML form of the statement rather than doing the select first.

Obviously this is most beneficial with complex queries with many joins and conditions, but I will use a few simple examples to illustrate my point.  Let’s assume we have a table called CUSTOMER that contains customers and has a column customer_id to identify the customer and a column called status which can be set to either ‘A’ (active) or ‘I’ (inactive).

Say we want to delete the inactive customers, first do a select:

SELECT customer_id, status
FROM CUSTOMER c
WHERE c.status = ‘I’

We can then evaluate these results and make sure that they contain only the customers we want to delete.

We can then easily turn this into a delete statement by copying the FROM and WHERE clauses and adding DELETE:

DELETE c
FROM CUSTOMER c
WHERE c.status = ‘I’

Now, consider if instead of deleting these customers, we wanted to activate them instead.  Once again we can re-use the FROM and WHERE clause from our select which we know contains exactly the set of data we want to change:

UPDATE c
SET status = ‘A’
FROM CUSTOMER c
WHERE c. status = ‘I’

Finally, we can take the same SELECT, FROM and WHERE clause and use it in an INSERT statement.  Say we want to insert the inactive customers into an archive table, to clean up our active customer table in our OLTP system:

INSERT INTO archive_CUSTOMER
(customer_id, status)
SELECT customer_id, status
FROM CUSTOMER c
WHERE c.status = ‘I’

Even though turning a SELECT statement into an INSERT statement, it is still useful to run your SELECT first to ensure you have the data you are expecting to return before you actually insert the data into the new table.

Most times when creating my update or delete statements in a script I comment out my select portion, so that I can go back and troubleshoot a query if needed even after I have changed it into a DML query by simply highlighting everything after SELECT and running the query:

UPDATE c
SET status = ‘A’
–SELECT customer_id, status
FROM CUSTOMER c
WHERE c. status = ‘I’

This looks like a lot of extra work for these simple queries, but if you get in the habit of doing it for all queries, you will see the benefits when you are working on some more complex DML statements.