Oracle DELETE SQL Statement – The Complete Guide

The DELETE SQL statement allows you to delete records from a table. I’ll explain more about the Oracle DELETE SQL statement in this article.

What Is The Oracle DELETE SQL Statement?

The Oracle DELETE SQL statement, or delete query, is a statement you can run to delete records from a table. A WHERE clause is used to specify the criteria, and any rows matching this criteria will be deleted. You can use it to delete a single record, multiple records, or all records in a table.

How Can I Write a DELETE Query in SQL?

The DELETE query in SQL has some pretty simple syntax:

DELETE FROM [ table | (subquery) ] alias [WHERE condition];

Let me explain this a bit further.

You start with DELETE FROM. There is no need to specify the columns here – we’re working on deleting rows so the columns don’t matter.

Then, you specify either a table or a subquery. The table here can be either a table, a view, or a materialized view.

Then, you can give the table an alias, to help when writing your WHERE condition.

After this, you can write WHERE then a condition. The condition allows you to specify which rows to delete. If you only want to delete some of the rows in a table, you’ll need the WHERE condition. If you want to delete all rows in a table, don’t use a WHERE condition.

Examples of an Oracle DELETE Query in SQL

Let’s see some examples. First, let’s see some sample data that we can run our DELETE SQL statements on.

SELECT student_id, first_name, last_name
FROM student;

SELECT student_id, first_name, last_name
FROM student;

STUDENT_ID

FIRST_NAME

LAST_NAME

1

John

Smith

2

Susan

Johnson

3

Tom

Capper

4

Mark

Holloway

5

Steven

Webber

6

Julie

Armstrong

7

Michelle

Randall

8

Andrew

Cooper

9

Robert

Pickering

10

Tanya

Hall

11

Jarrad

Winston

12

Mary

Taylor

100

Mark

Anderson

102

John

Rogers

Example 1 – Simple Delete

This is an example of deleting a single record.

DELETEFROM student
WHERE student_id =1;

DELETE FROM student
WHERE student_id = 1;

The record that has the student_id of 1 is deleted. All other rows remain in the table.

Can You Delete Data Based On Data In Another Table?

Yes, you can.

Let’s explain what I mean.

Say, for example, you had your student table. And let’s say you wanted to delete all student records that were not in any subjects. This data, though, is in the student_subject table, which links students to subjects.

But, we can’t specify more than one table in the Oracle DELETE statement, right?

Actually, you can. You just can’t delete from more than one table at a time.

DELETE FROM student
WHERE student_id NOT IN (
SELECT student_subject.student_id
FROM student_subject
);

As you can see, I just replaced the DELETE with a SELECT *.

My Oracle DELETE Statement Is Too Slow!

Are you trying to delete data from a table but it’s too slow?

Well, there are a few things you can do.

First, if you’re deleting all of the data in the table, it may be better to use the TRUNCATE TABLE statement. It’s faster (because it doesn’t write to a log and allow for rollbacks), but you can’t undo it, so you need to be sure. You also can’t specify a WHERE condition with a TRUNCATE TABLE statement.

If you can’t use TRUNCATE, you can try to filter your data differently or use indexes on your table.

Another method is actually creating a new table with the data you want to keep. You can do this by :

Write a CREATE TABLE AS SELECT method and then specifying the WHERE clause for the records you want to keep

Delete the original table

Rename the new table to the old table

So, that’s how you run an Oracle DELETE SQL statement. You can delete a single row, multiple rows, or the entire table, using WHERE conditions which can involve subqueries.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!