SQL DELETE






You already know how to retrieve, insert and update data in SQL database table. In this chapter weíll learn how to delete data from a table using the SQL DELETE command.

Using our Users table we will illustrate the SQL DELETE usage. One of the users in the Users table (Stephen Grant) has just left the company, and your boss has asked you to delete his record. How do you do that? Consider the SQL statement below:

DELETE FROM Users
WHERE LastName = 'Grant'

The first line in the SQL DELETE statement above specifies the table that we are deleting the record(s) from. The second line (the WHERE clause) specifies which rows exactly do we delete (in our case all rows which has LastName of ĎGrantí). As you can see the DELETE SQL queries have very simple syntax and in fact are very close to the natural language. But wait, there is something wrong with the statement above! The problem is that we have more than one user having last name of ĎGrantí, and all users with this last name will be deleted. Because we donít want to do that, we need to find a table field or combination of fields that uniquely identifies the user Stephen Grant. Looking at the Users table an obvious candidate for such a unique field is the Email column (itís not likely that different users use one and the same email). Our improved SQL query which deletes only the record of Stephen Grantís record will look like this:

DELETE FROM Users
WHERE Email = 'sgrant@sgrantemail.com'

What happens if you donít specify a WHERE clause in your DELETE query?

DELETE FROM Users

The answer is that all records in the Users table will be deleted. The SQL TRUNCATE statement below will have the exact same effect as the last DELETE statement:

TRUNCATE TABLE Users

The TRUNCATE statement will delete all rows in the Users table, without deleting the table itself.

Be very careful when using DELETE and TRUNCATE, because you cannot undo these statements, and once row(s) are deleted fro your table they are gone forever if you donít have a backup.


SQL Tutorial

SQL Tutorial
SQL Introduction
RDBMS and Tables
SQL SELECT
SQL WHERE
SQL INSERT
SQL DISTINCT
SQL UPDATE
SQL DELETE
SQL ORDER BY
SQL Aggregate Functions
SQL GROUP BY
SQL HAVING
Relations, Keys, and Normalization
SQL JOIN
SQL UNION
SQL Nested Queries
CREATE DATABASE, CREATE TABLE and ALTER TABLE
SQL Views
SQL Indexes
SQL Training
SQL Hosting
SQL Replication
Contact Us
Legal
SQL Resources
Copyright © Art Branch Inc
SQL Tutorial | Bookmark | Contact | Link