SQL — UPDATE INTO another table

SQL — update into another table

Update records from one table and insert the updated records into another table in ONE statement

“Why would I need a query like that?” An update into statement is advantageous for two main reasons:

• The statement is Atomic; either both occur or nothing occurs, i.e. if either the update or the insert fail, it rolls back the changes.
• It’s cheaper: your database only has to look up the records once. Alternatively, executing a separate insert and delete take two lookups
• Bragging rights: impress your boss and coworkers

Convinced? “Show me some code already!”. Okay, but first we’ll have to set up some tables to demonstrate the query on. Lets code:

Setup: create some tables with data

Imagine we have a news website with a lot of articles. If a users notices a spelling error in one of the articles he/she can submit an error via the website. The errors get stored in a table called SpellingErrors. We’ll store the Id of the article that contains the error, along with a message and email address of the reporter. We store the email address so that we can contact the reporter for questions.

When one of our writers fixes the spelling error we update the record setting the email address to NULL; we don’t need it anymore and we’d like to guarantee the privacy of the reporter. In addition we’d like to keep some information about the error so we can horribly punish writers that make a lot of mistakes. In order to do this we’ll store some data into a table called SpellingErrorsHistory. Let’s first create the tables and insert some Errors.

Execute these queries. f you then select from the SpellingErrors table you’ll see the following:

Performing our query

Lets get to creating the query you’re here for! Let’s check out the query and then explain what it’s doing.

One of our writers has fixed the error with Id 1. We’ll store that in a variable called @targetId. Then we UPDATE the SpellingErrors table and set the ReporterEmail to NULL where the Id is equal to the @targetId (1). Using the OUTPUT we have access to the columns we’ve updated from the SpellingsErrors table. We OUTPUT some columns, along with an extra one (SolvedMessage) into the SpellingErrorsHistory table. Also notice that we don’t save users’ email data in this table.

Congratulations, you’ve done it! This is an easy, safe query that allows you to perform two queries at once.

Conclusion

Using this query we can perform an atomic operation that updates records from one table while simultaneously inserting those updated records into another one. Also check out:

• DELETE INTO another table
• Undoing queries using transactions
• Insert, delete and update in ONE statement
• UPDATE SELECT a batch of records
• Version control your database

Happy coding!

— Mike

P.S: like what I’m doing? Follow me!