Problem
This is third article in the series regarding different behavior and syntax for SQL Server, Oracle and PostgreSQL. In this article we will look at the SQL DELETE statement.
Solution
As we have already seen with the Updates and Inserts SQL statements, the DELETE statement syntax can vary in the different SQL platforms as well as perform differently, but the DELETE statement is one of the core DBMS DML (Data Manipulation Language) statements.
For test purposes I will use the github freely downloadable database sample Chinook, as it is available in multiple RDBMS formats for download. It is a simulation of a digital media store, with some sample data, all you have to do is download the version you need and you have all the scripts for the data structure and the data.
SQL DELETE Basics
The basic syntax for the DELETE statement is quite simple and is the same for SQL Server, Oracle and PostgreSQL where we specify the DELETE command, name of the table and WHERE condition.
delete
from Artist
where ArtistId=276;
It is strongly recommended to always have a WHERE clause for any delete operation, if you want to delete all table data it is better to use the TRUNCATE TABLE statement, because in all three RDBMS the TRUNCATE command is minimally logged (a row is not logged in the archive log or transaction log for every row deleted, but instead limited data is logged for the whole table) and thus is faster and uses less resources.
Something that I like to do before performing a DELETE on existing records is always execute a SELECT with the same query in order to check what the delete statement removes and be sure to check the result set before launching the command.
Another good deletion practice for SQL Server and PostgreSQL is to wrap the statement in an explicit transaction so that it will not be autocommited by default, but we will need to actually specify a COMMIT. That is not needed in Oracle as autocommit is not the default and you are always in an explicit transaction, thus you can ROLLBACK or COMMIT. This behavior can be tricky for DBA and developers coming from other RDBMS. I remember that once I created some locks in a table because I didn’t commit a massive UPDATE! The same applies for people coming from Oracle and expecting to have the possibility to ROLLBACK is even more dangerous! We will see examples of this in the remaining examples.
DELETE Data Using a Join and an Explicit Transaction
Another possibility to limit and choose the rows to delete is joining (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, multiple-table JOINs) with other tables. For example, let’s imagine that we would like to delete rows that were inserted in the INSERT tip in the table Discount_table, based on customers from Austria that have bought Rock and Metal genres.
SQL Server
BEGIN TRANSACTION
DELETE
FROM dbo.Discount_Table
INNER JOIN dbo.Invoice ON Invoice.CustomerId = Discount_Table.Customerid
INNER JOIN dbo.InvoiceLine ON InvoiceLine.InvoiceId = Invoice.InvoiceId
INNER JOIN dbo.Customer ON Customer.CustomerId = Discount_Table.Customerid
INNER JOIN dbo.Track ON Track.TrackId = InvoiceLine.TrackId
WHERE dbo.Customer.Country='Austria' AND GenreId IN (1,3)
Here’s the result of our DELETE:
And since we are happy that it is only one row as expected we can now COMMIT:
commit
Oracle
Now let’s see the way in which the same thing can be achieved in Oracle, as with the UPDATE with joins of my previous tip, on Oracle we have to do a different thing as a DELETE with joins is not allowed:
DELETE
from DISCOUNT_TABLE
where customerid in
(select discount_table.customerid FROM Discount_Table
INNER JOIN Invoice ON Invoice.CustomerId = Discount_Table.Customerid
INNER JOIN InvoiceLine ON InvoiceLine.InvoiceId = Invoice.InvoiceId
INNER JOIN Customer ON Customer.CustomerId = Discount_Table.Customerid
INNER JOIN Track
ON Track.TrackId = InvoiceLine.TrackId
WHERE Customer.Country='Austria' AND GenreId IN (1,3));
And then commit it:
commit;
As you can see, we cannot use the same syntax nor the same style with joins, if we try to do something like this:
DELETE
from
(select discount_table.* FROM Discount_Table
INNER JOIN Invoice ON Invoice.CustomerId = Discount_Table.Customerid
INNER JOIN InvoiceLine ON InvoiceLine.InvoiceId = Invoice.InvoiceId
INNER JOIN Customer ON Customer.CustomerId = Discount_Table.Customerid
INNER JOIN Track ON Track.TrackId = InvoiceLine.TrackId
WHERE Customer.Country='Austria' AND GenreId IN (1,3));
We end up having an error that is quite self-explanatory as Oracle tries to protect from deleting more rows that it cannot clearly identify:
Error at Command Line : 3 Column : 1
Error report -
SQL Error: ORA-01752: cannot delete from view without exactly one key-preserved table
01752. 00000 - "cannot delete from view without exactly one key-preserved table"
*Cause: The deleted table had
- no key-preserved tables,
- more than one key-preserved table, or
- the key-preserved table was an unmerged view.
*Action: Redefine the view or delete it from the underlying base tables.
So, as we’ve seen in the example above, we must do a different things in order to get only the number of rows that we want to delete. By the way this is the standard ANSI SQL behavior as instead of joins we should use a subquery or an IN clause in order to identify the rows to be deleted. Obviously, this same style of using a subquery, as it is standard ANSI SQL, is also possible in SQL Server.
PostgreSQL
Now let’s see the behavior of PostgreSQL. Here we have basically two ways as in SQL Server, one is to use the USING clause which again is not standard and is basically a JOIN as in SQL Server, the other is to use a subquery:
begin;
DELETE
from "discount_table"
where "customerid" in
(select "discount_table"."customerid" FROM "discount_table"
INNER JOIN "Invoice" ON "Invoice"."CustomerId" = "discount_table"."customerid"
INNER JOIN "InvoiceLine" ON "InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId"
INNER JOIN "Customer" ON "Customer"."CustomerId" = "discount_table"."customerid"
INNER JOIN "Track" ON "Track"."TrackId" = "InvoiceLine"."TrackId"
WHERE "Customer"."Country"='Austria' AND "GenreId" IN (1,3));
Since we’d like to demonstrate other possible syntax, we will rollback this transaction:
rollback;
Delete Data using a CTE
Let’s now introduce something that is possible in all three RDBMS, the use of a CTE instead of a subquery.
PostegreSQL
begin;
with rows_to_delete as
(select distinct "discount_table".* FROM "discount_table"
INNER JOIN "Invoice" ON "Invoice"."CustomerId" = "discount_table"."customerid"
INNER JOIN "InvoiceLine" ON "InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId"
INNER JOIN "Customer" ON "Customer"."CustomerId" = "discount_table"."customerid"
INNER JOIN "Track" ON "Track"."TrackId" = "InvoiceLine"."TrackId"
WHERE "Customer"."Country"='Austria' AND "GenreId" IN (1,3))
DELETE
from "discount_table"
using rows_to_delete
where "discount_table"."customerid"=rows_to_delete."customerid";
As you can see, we have joined the CTE with the USING clause which as I said before is not ANSI SQL standard.
Now we can commit this delete:
commit;
SQL Server
We’ve just seen in the example before that a CTE can be used in order to filter the rows to be deleted in PostgreSQL, now we’ll see the syntax in SQL Server, which is pretty much the same with the SELECT statement and WHERE condition, so using the same example (and having re-inserted the deleted row in the discount_table):
begin tran
;with rows_to_delete as
(select distinct discount_table.* FROM discount_table
INNER JOIN Invoice ON Invoice.CustomerId = discount_table.customerid
INNER JOIN InvoiceLine ON InvoiceLine.InvoiceId = Invoice.InvoiceId
INNER JOIN Customer ON Customer.CustomerId = discount_table.customerid
INNER JOIN Track ON Track.TrackId = InvoiceLine.TrackId
WHERE Customer.Country='Austria' AND GenreId IN (1,3))
DELETE
from discount_table
from Discount_Table
inner join rows_to_delete
on discount_table.customerid=rows_to_delete.customerid
We can now rollback the transaction as I’d like to use this row again for the next example:
rollback;
Oracle
In Oracle the syntax is a little bit different as the CTE must not be declared first, but after the DELETE statement as in this example:
DELETE
from chinook.discount_table
where customerid in
(with rows_to_delete as
(select distinct chinook.discount_table.* FROM chinook.discount_table
INNER JOIN chinook.Invoice ON chinook.Invoice.CustomerId = chinook.discount_table.customerid
INNER JOIN chinook.InvoiceLine ON chinook.InvoiceLine.InvoiceId = chinook.Invoice.InvoiceId
INNER JOIN chinook.Customer ON chinook.Customer.CustomerId = chinook.discount_table.customerid
INNER JOIN chinook.Track ON chinook.Track.TrackId = chinook.InvoiceLine.TrackId
WHERE chinook.Customer.Country='Austria' AND GenreId IN (1,3))
select customerid from rows_to_delete);
And we can commit it:
commit;
Return Output from Deleted Rows
Let’s see now another important feature common on all three RDBMS, the possibility of returning output for deleted rows, exactly as I’ve already shown on my previous INSERT tip.
SQL Server
begin tran
;with rows_to_delete as
(select distinct discount_table.* FROM discount_table
INNER JOIN Invoice ON Invoice.CustomerId = discount_table.customerid
INNER JOIN InvoiceLine ON InvoiceLine.InvoiceId = Invoice.InvoiceId
INNER JOIN Customer ON Customer.CustomerId = discount_table.customerid
INNER JOIN Track ON Track.TrackId = InvoiceLine.TrackId
WHERE Customer.Country='Austria' AND GenreId IN (1,3))
DELETE
from discount_table
output deleted.*
from Discount_Table
inner join rows_to_delete
on discount_table.customerid=rows_to_delete.customerid
In this way we can output the row that has been deleted, we can also put this result into table. Below we will use a temp table to store the data.
begin tran
create table #temp_deleted
(
[Customerid] [int] NULL,
[Genretotal] [numeric](10, 2) NULL,
[DiscountPercentage] [tinyint] NULL
)
;with rows_to_delete as
(select distinct discount_table.* FROM discount_table
INNER JOIN Invoice ON Invoice.CustomerId = discount_table.customerid
INNER JOIN InvoiceLine ON InvoiceLine.InvoiceId = Invoice.InvoiceId
INNER JOIN Customer ON Customer.CustomerId = discount_table.customerid
INNER JOIN Track ON Track.TrackId = InvoiceLine.TrackId
WHERE Customer.Country='Austria' AND GenreId IN (1,3))
DELETE
from discount_table
output deleted.*
into #temp_deleted
from Discount_Table
inner join rows_to_delete
on discount_table.customerid=rows_to_delete.customerid
Let’s check the temp table:
SELECT*
FROM #temp_deleted
Now we can finally commit:
commit
Oracle
Let’s take a look at the Oracle syntax, as with INSERT statement the keyword here is RETURNING instead of OUTPUT:
SET SERVEROUTPUT ON
DECLARE
my_CustomerId chinook.discount_table.customerid%TYPE;
my_Genretotal chinook.discount_table.genretotal%TYPE;
my_discountpercentage chinook.discount_table.discountpercentage%TYPE;
BEGIN
DELETE
from chinook.discount_table
where customerid in
(select discount_table.customerid FROM Discount_Table
INNER JOIN Invoice ON Invoice.CustomerId = Discount_Table.Customerid
INNER JOIN InvoiceLine ON InvoiceLine.InvoiceId = Invoice.InvoiceId
INNER JOIN Customer ON Customer.CustomerId = Discount_Table.Customerid
INNER JOIN Track ON Track.TrackId = InvoiceLine.TrackId
WHERE Customer.Country='Austria' AND GenreId IN (1,3))
returning customerid, genretotal,discountpercentage into my_CustomerId,my_Genretotal, my_discountpercentage;
dbms_output.put_line(my_CustomerId ||' '||my_Genretotal||' '||my_discountpercentage);
end;
Please note the SET SERVEROUTPUT ON instruction is used to display the results with dbms_output.put_line and the way I declared the “my” variables to store the DELETE output, using the same type as the table. This can be useful in many situations in Oracle and I used this same style on my previous INSERT tip. Now we can finally commit and delete this row:
commit;
PostegreSQL
Now let’s take a look at the PostgreSQL syntax, again we see the use of RETURNING and we have the same syntax used in the INSERT tip:
begin;
DELETE
from "discount_table"
where "customerid" in
(select "discount_table"."customerid" FROM "discount_table"
INNER JOIN "Invoice" ON "Invoice"."CustomerId" = "discount_table"."customerid"
INNER JOIN "InvoiceLine" ON "InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId"
INNER JOIN "Customer" ON "Customer"."CustomerId" = "discount_table"."customerid"
INNER JOIN "Track" ON "Track"."TrackId" = "InvoiceLine"."TrackId"
WHERE "Customer"."Country"='Austria' AND "GenreId" IN (1,3))
returning "discount_table"."customerid", "genretotal","discountpercentage" ;
And we can finally commit in PostgreSQL:
commit;
Summary
We have seen in this tip the various syntaxes and possibilities related to the statement DELETE in three DBMS, this is part 3 of a series, please find the other links below.
Next Steps
- Link to Update with joins in the three different RDBMS
- Link to INSERT in the three different RDBMS
- As mentioned at the beginning, there are also performance differences for the DELETE statement, in particular it seems that PostgreSQL is a lot faster, but there’s a reason for this as you can see in the comments section of this tip: SQL Server vs MySQL vs PostgreSQL Delete Performance Comparison. In fact records are not directly deleted in PostgreSQL, but only marked as deleted. The explicit vacuum is the instruction that in PostgreSQL clears the dead tuples and releases the space to the table, until that moment the space occupied by the deleted tuples cannot be reused, so this is a big difference from the other RDBMS. Note that the vacuum can be automated using autovacuum, otherwise it can be invoked manually even for a single table. Here is the official documentation of autovacuum.
- Another interesting feature in Oracle is the possibility of using the CASCADE keyword in order to delete rows referenced as Foreign Keys in other tables, so that the DELETE will remove in just one step rows from the main table and also the rows referenced. You can see an example in this tutorial at the point D. This can also be done in SQL Server as shown here.
