How to use multi-table delete in SQL

We know how to delete data from a single table, however, if you are confronted with a situation where you want to delete data from one table and also any related data from other tables, you can employ the multi-table delete in SQL. An example scenario can be that when we delete an actor from our Actors table we also want all rows in the digital assets table belonging to the deleted actor removed too.

Older Syntax :

DELETE T1, T2
FROM T1, T2, T3
WHERE <condition>

Newer Syntax :

Use the newer syntax as it reads better:

DELETE FROM T1, T2
USING T1, T2, T3
WHERE <condition>

-- Query 1
DELETE Actors, DigitalAssets
-- Mention tables to delete rows from
FROM Actors
-- The inner join creates a derived table with matching rows from both tables
INNER JOIN DigitalAssets
ON Actors.Id = DigitalAssets.ActorId
WHERE AssetType = "Twitter";

-- Query 2
DELETE FROM Actors, DigitalAssets USING Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorId WHERE AssetType = "Twitter";

-- Query 3
DELETE Actors FROM Actors WHERE EXISTS ( SELECT * FROM Actors INNER JOIN DigitalAssets ON Id = ActorId WHERE AssetType="Twitter");

-- Query 4
DELETE Actors FROM Actors WHERE EXISTS (SELECT * FROM DigitalAssets WHERE ActorId = Id AND AssetType = "Twitter");

-- Query 5
DELETE Actors, DigitalAssets
-- specify the tables to delete from
FROM Actors, DigitalAssets
-- reference tables
WHERE ActorId = Id
-- conditions to narrow down rows
AND FirstName = "Johnny"
AND AssetType != "Pinterest";

DELETE Actors, DigitalAssets FROM Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorId WHERE AssetType = “Twitter”;

Imagine we want to delete actors who have a Twitter account. At the same time, we also want to remove their Twitter account information from our digital assets table. We can delete intended rows from both tables as follows:

DELETE Actors, DigitalAssets FROM Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorId WHERE AssetType = "Twitter";

You can observe from the output that rows from both tables get deleted. One way to think about the query is to realize that all the rows that are returned by the inner join of the two tables based on the joining criteria and the where condition are deleted from both the tables.
The alternative and newer syntax appears below:

DELETE FROM Actors, DigitalAssets USING Actors INNER JOIN DigitalAssets ON Actors.Id = DigitalAssets.ActorId WHERE AssetType = "Twitter";

DELETE column1 FROM Actors WHERE EXISTS ( SELECT * FROM table1 INNER JOIN table2 ON Id = column2 WHERE column3=”Twitter”);

Consider the query below which attempts to delete only the rows from the Actor table with Twitter accounts but fails:

DELETE Actors FROM Actors WHERE EXISTS ( SELECT * FROM Actors INNER JOIN DigitalAssets ON Id = ActorId WHERE AssetType="Twitter");

The above query fails because MySQL disallows rows to be deleted from a table if the same table also appears in the SELECT clause, i.e., we can’t delete from a table that’s read from in a nested subquery. In this case, the Actors table also appears in the inner query’s SELECT clause. The same query is rewritten as a correlated query works:

DELETE Actors FROM Actors WHERE EXISTS (SELECT * FROM DigitalAssets WHERE ActorId = Id AND AssetType = "Twitter");

DELETE table1, table2 FROM table1, table2 WHERE column1 = Id AND column2 = “Johnny” AND column3 != “Pinterest”;

As another example, say we want to remove Johnny Depp from the Actors table and all his accounts except for his Pinterest from the DigitalAssets table at the same time. We can write a multi-table delete statement as follows:

DELETE Actors, DigitalAssets FROM Actors, DigitalAssets WHERE ActorId = Id AND FirstName = "Johnny" AND AssetType != "Pinterest";

ORDER BY and LIMIT clauses can’t be used with multi-table deletes.

Multi-Table Update

We have an equivalent of updating multiple tables just as we can delete from multiple tables.

Syntax #
UPDATE T1, T2
SET col1 = newVal1, col2 = newVal2
WHERE <condition1>

-- The queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
UPDATE
Actors INNER JOIN DigitalAssets ON Id = ActorId SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL) WHERE AssetType = "Facebook";
-- Query 2
UPDATE Actors, DigitalAssets SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL)WHERE AssetType = "Facebook" AND ActorId = Id;

UPDATE Actors INNER JOIN DigitalAssets ON Id = ActorId SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL) WHERE AssetType = “Facebook”;

Let’s say we want to write an update query that converts the FirstName and SecondName strings stored in the Actors table to upper case for those actors who are on Facebook, and at the same time we also want to convert the associated Facebook URL to uppercase. We can update rows in both the tables using a multi update query as follows:

UPDATE Actors INNER JOIN DigitalAssets ON Id = ActorId SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL) WHERE AssetType = "Facebook";

We performed an inner join to find those actors with Facebook presence. The matching rows from both the tables get updated as observed in the screen-shot. Instead of using an inner join, we can write the same query using the WHERE clause as follows:

UPDATE Actors, DigitalAssets SET FirstName = UPPER(FirstName), SecondName = UPPER(SecondName), URL = UPPER(URL) WHERE AssetType = "Facebook" AND ActorId = Id;

Similarly to multi deleted, we can’t update a table that is also being read from in a subquery.

ORDER BY and LIMIT clauses can’t be used with multi table deletes.

SELECT and INSERT

MySQL provides us the facility to insert several rows from another table into an existing table using a combination of select and insert statements. In fact, we can also create a table on the fly and fill it up with rows from another table.

Syntax to Insert in an Existing Table :
INSERT INTO table1 (col1, col2) SELECT col3, col4 FROM table2;

Syntax to Insert in a New Table :
CREATE TABLE newTable (col1 <datatype>, <col2>) SELECT col3, col4FROM table2;

-- Query 1
CREATE TABLE Names (name VARCHAR(20), PRIMARY KEY(name));

-- Query 2
INSERT INTO Names(name) SELECT SecondName FROM Actors;

-- Query 3
INSERT IGNORE INTO Names(name) SELECT SecondName FROM Actors WHERE Id = 1;

-- Query 4
CREATE TABLE MyTempTable SELECT * FROM Actors;

-- Query 5
CREATE TABLE NamesWithDoBs ( Id INT AUTO_INCREMENT, Name VARCHAR(20) NOT NULL DEFAULT "unknown", DoB DATE, PRIMARY KEY(Id), KEY(Name), KEY(DoB)) SELECT FirstName, DoB FROM Actors;

-- Query 6
CREATE TABLE CopyOfActors LIKE Actors;

CREATE TABLE table (column1 VARCHAR(20), PRIMARY KEY(column1));

We can populate data into a table from another table using INSERT and SELECT in a single query. Say, we want to create a table of all the second names of actors. We’ll create a table as follows:

CREATE TABLE Names (name VARCHAR(20), PRIMARY KEY(name));

Now we can insert using INSERT and SELECT in a single statement as follows:

INSERT INTO Names(name) SELECT SecondName FROM Actors;

INSERT IGNORE INTO table1(column1) SELECT column2 FROM table2 WHERE column3 = 1;

Note that in creating the table Names we have set the only column as the primary key, however, we aren’t guaranteed that the values being selected from the Actors table will not contain duplicates. Let’s try to insert an existing row into the table:

As you can see, trying to add a duplicate rows result in an error.MySQL provides a way to bypass this error and continue execution using the IGNORE clause. It doesn’t mean a duplicate row is added to the table, rather it means that MySQL issues a warning instead of issuing an error and aborting.

INSERT IGNORE INTO Names(name) SELECT SecondName FROM Actors WHERE Id = 1;

Note that the query finishes successfully but informs the user about the duplicate row. Both the duplicate and warning counts read one.

CREATE TABLE table2 SELECT * FROM table1;

In one of the previous examples we created the table first and then inserted data into the table. We can do both tasks in one shot. Consider the following query:

CREATE TABLE MyTempTable SELECT * FROM Actors;

As you can see we get a copy of the data with the above query but if you pay attention to the describe statement, you’ll notice that the table we create doesn’t inherit the primary key constraints. In fact, creating and copying data as above will not create foreign or primary key constraints on the copy table.

CREATE TABLE table2 ( column1 INT AUTO_INCREMENT, column2 VARCHAR(20) NOT NULL DEFAULT “unknown”, column3 DATE, PRIMARY KEY(column1), KEY(column2), KEY(column3)) SELECT column4, column5 FROM table1;

All the modifiers that can be used in a stand-alone create table statement can also be used in a combined create and populate table statement.

CREATE TABLE NamesWithDoBs ( Id INT AUTO_INCREMENT, Name VARCHAR(20) NOT NULL DEFAULT "unknown", DoB DATE, PRIMARY KEY(Id), KEY(Name), KEY(DoB)) SELECT FirstName, DoB FROM Actors;

CREATE TABLE CopyOfActors LIKE Actors;

We can also create a copy of an existing table without the data using the LIKE operator. For instance:

CREATE TABLE CopyOfActors LIKE Actors;

As you can see, the copy table doesn’t contain any data, but its structure is exactly the same as the source table. The primary keys and any indexes defined on the source table are also defined on the copy table.

REPLACE

REPLACE is much like the INSERT statement with one key difference: we can’t insert a row if a table already contains a row with the same primary key. However, REPLACE allows us the convenience of adding a row with the same primary key as an existing row in the table. Under the hood, REPLACE deletes the row and then adds the new row thereby maintaining the primary key constraint at all times. Sure, we can also use the UPDATE clause to achieve the same effect. However, REPLACE can be useful in automated scripts where it is not known ahead of time if a particular table already contains a particular primary key. If it doesn’t, the replacement behaves like an insertion, otherwise, it deletes and writes in the new row with the same primary key.

REPLACE INTO table (col1, col2, ... coln)
VALUES (val1, val2, ... valn)
WHERE <condition>

-- The queries are reproduced below for convenient copy/paste into the terminal.

-- Query 1
REPLACE INTO Actors (Id, FirstName, SecondName,DoB, Gender, MaritalStatus, NetworthInMillions) VALUES (3, "George", "Clooney", "1961-05-06","Male", "Married", 500.00);

-- Query 2
REPLACE INTO Actors (Id) VALUES (3);

-- Query 3
REPLACE INTO Actors SET id = (SELECT Id FROM Actors WHERE FirstName="Brad");

REPLACE INTO table1 (column1, column2, column3,column4, column5, column5, column6) VALUES (3, “George”, “Clooney”, “1961-05-06″,”Male”, “Married”, 500.00);

We can use all the variations of the INSERT clause with REPLACE too. Let’s start with a simple example, where we want to replace the actor with the ID equal to 3 in the Actors table.

REPLACE INTO Actors (Id, FirstName, SecondName,DoB, Gender, MaritalStatus, NetworthInMillions) VALUES (3, "George", "Clooney", "1961-05-06","Male", "Married", 500.00);

You can observe that the output of the replace query says 2 rows affected, which implies one row was deleted and a second was inserted.

REPLACE INTO Actors (Id) VALUES (3);

Now we’ll repeat the previous query but only provide the value for the primary key column and observe the outcome.

REPLACE INTO Actors (Id) VALUES (3);

As you can see, the rest of the columns not specified in the query end up taking the default value which is NULL.

If a table doesn’t have a primary key defined, REPLACE behaves exactly like an INSERT. Without a primary key REPLACE can’t uniquely identify a row to replace.

Remember that when inserting the duplicate row using the INSERT IGNORE clause, the duplicate row is ignored and not added to the table whereas when using REPLACE the existing row is deleted and the duplicate row is added to the table.
Similar to multi-delete and update, we can’t replace it into a table that is also being read from a subquery. For instance, the following query, which replaces the ID of a row with itself gives an error:

REPLACE INTO ActorsSET id = (SELECT Id FROM Actors WHERE FirstName="Brad");