SQL — update into another table

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

There’s some work to do on our tables (Photo by Mabel Amber on Pexels)

“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.