Sql DELETE

Sql DELETE
Nikhil
Published on 2021-08-14 15:50:10

The DELETE statement is used to delete records from a table.

DELETE all rows

Omitting a WHERE clause will delete all rows from a table.

DELETE FROM Employees

See TRUNCATE documentation for details on how to TRUNCATE performance can be better because it ignores triggers and indexes and logs to just delete the data.

DELETE certain rows with WHERE

This will delete all rows that match the WHERE criteria.

DELETE FROM Employees
WHERE FName = 'John'

TRUNCATE clause

Use this to reset the table to the condition at which it was created. This deletes all rows and resets values such as auto-increment. It also doesn't log each individual row deletion.

TRUNCATE TABLE Employees

DELETE certain rows based upon comparisons with other tables

It is possible to DELETE data from a table if it matches (or mismatches) certain data in other tables.

Let's assume we want to DELETEdata from Source once it's loaded into Target.

DELETE FROM Source
WHERE EXISTS ( SELECT 1 -- specific value in SELECT doesn't matter
FROM Target
Where Source.ID = Target.ID )

Most common RDBMS implementations (e.g. MySQL, Oracle, PostgresSQL, Teradata) allow tables to be joined during DELETE allowing more complex comparison in a compact syntax.

Adding complexity to the original scenario, let's assume Aggregate is built from Target once a day and does not contain the same ID but contains the same date. Let us also assume that we want to delete data from Source only after the aggregate is populated for the day.

On MySQL, Oracle, and Teradata this can be done using:

DELETE FROM Source
WHERE Source.ID = TargetSchema.Target.ID
AND TargetSchema.Target.Date = AggregateSchema.Aggregate.Date
In PostgreSQL use:

DELETE FROM Source USING TargetSchema.Target, AggregateSchema.Aggregate WHERE Source.ID = TargetSchema.Target.ID
AND TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate

This essentially results in INNER JOINs between Source, Target, and Aggregate. The deletion is performed on Source when the same IDs exist in Target AND date present in Target for those IDs also exists in Aggregate. The same query may also be written (on MySQL, Oracle, Teradata) as:

DELETE Source
FROM Source, TargetSchema.Target, AggregateSchema.Aggregate
WHERE Source.ID = TargetSchema.Target.ID
AND TargetSchema.Target.DataDate = AggregateSchema.Aggregate.AggDate

Explicit joins may be mentioned in Delete statements on some RDBMS implementations (e.g. Oracle, MySQL) but not supported on all platforms (e.g. Teradata does not support them) Comparisons can be designed to check mismatch scenarios instead of matching ones with all syntax styles (observe NOT EXISTS below)

DELETE FROM Source
WHERE NOT EXISTS ( SELECT 1 -- specific value in SELECT doesn't matter
FROM Target
Where Source.ID = Target.ID )

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website