SQL — DELETE INTO another table

Delete records from one table and insert them into another table in ONE statement

Delete this Sansevieria from one pot INTO the other! (Photo by Cottonbro on Pexels)

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

• The statement is Atomic; either both occur or nothing occurs, i.e. if either the delete 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

As an example we’ll imagine we have a company with a lot of processes. Each process logs its errors into a table called ErrorMessages in order to keep track. Once an error is solved, expired or otherwise irrelevant anymore we can remove it from the ErrorMessages. We’d like to save the Error information though, so we can later analyse which process fails the most. For this we’ll create the ErrorMessageBackup. Our goal is to delete from ErrorMessages into the ErrorMessageBackup table. Lets create our tables first and insert some data into our ErrorMessages.

Executing these queries we see that the content of the ErrorMessages table looks like this:

Performing our query

This is where the magic starts. We want to take out the 404 error from our database. Let’s check out our query and then get into it a bit more.