Now that we’ve learned how to insert and update data in SQL, we need to learn how to delete it. Deleting data is a little different than the other commands we’ve looked at. Deleting doesn’t deal with fields. It is strictly about rows of data. If you want to delete the contents of a single field, this is actually an update. Let’s jump right in with a look at the syntax for deleting a row or record in SQL.

The Delete Statement

The DELETE SQL statement looks almost exactly like a SELECT statement. The only difference is it has no list of fields. Let’s start with a simple example:

SELECT * FROM CARDS WHERE CARDID > 5
SQL Delete

Much like we did with the UPDATE statement, starting with a SELECT helps us see what we’re actually going to erase. If we’re good with erasing these two rows, we can make one small change to turn this select into a DELETE.

DELETE FROM CARDS WHERE CARDID > 5
2 row(s) affected.

Much like the UPDATE statement, we don’t see any rows or anything visual. However, running the SELECT again will show no rows. We can remove our WHERE clause to see the state of the table after the delete has been processed.

SELECT * FROM CARDS
SQL Delete

As you can see, the cards with ID of six and seven have been removed from the system. Now, if you’re a little shocked that SQL didn’t ask if we wanted to delete before going ahead with it, good. You just learned a valuable lesson about the DELETE statement. It is very powerful and very dangerous. If you forget your WHERE clause, you can wipe out an entire table in one command.

Deletes and Joins

While a simple DELETE statement with a WHERE clause has its uses, you’ll probably want to delete data based on the results of a JOIN query at some point. Like most things in SQL, there are numerous ways to do this. We’ll focus on the one that is easiest to read and also to understand.

Perhaps you were wondering why we learned subqueries before moving into this lesson. The reason is that deleting with joins is much easier when we employ subqueries. It’s less dangerous and easier to understand for those who read our queries later.

SQL Delete

As always, we start with a SELET statement. Let’s say that the school decided to get rid of all two-credit classes. That means we need to erase any StudentClasses records that exist to take the students out of the classes that are being canceled.

As always, starting with a SELECT gets us off to a great start.

SELECT *
FROM Classes
INNER JOIN ClassInstance
ON Classes.ClassID = ClassInstance.ClassID
INNER JOIN StudentClasses
ON ClassInstance.ClassInstanceID = StudentClasses.ClassInstanceID
WHERE Credits = 2

It takes three tables to get the credits and the students with two credit classes into one place. The table we actually want to delete from is ClassInstance. Lets make a subquery out of this first.

SELECT *
FROM ClassInstance 
WHERE ClassInstanceID IN (

SELECT ClassInstanceID
FROM Classes
INNER JOIN ClassInstance
ON Classes.ClassID = ClassInstance.ClassID
INNER JOIN StudentClasses
ON ClassInstance.ClassInstanceID = StudentClasses.ClassInstanceID
WHERE Credits = 2

)

Notice that our WHERE IN clause and subquery select have the same field returned. This query will return the list of records that need to be deleted. The next step is to turn it into a DELETE statement.

DELETE
FROM ClassInstance 
WHERE ClassInstanceID IN (

SELECT ClassInstanceID
FROM Classes
INNER JOIN ClassInstance
ON Classes.ClassID = ClassInstance.ClassID
INNER JOIN StudentClasses
ON ClassInstance.ClassInstanceID = StudentClasses.ClassInstanceID
WHERE Credits = 2

)

Do we need to be scared about what this will delete? Not really. We just looked at what it’s going to delete. We can be pretty sure running this statement won’t do anything too crazy.

When You Can’t Delete

There are a few small situations where deleting rows is not permitted.

  • Deleting data would compromise data integrity - Some deletes aren’t allowed since they would break the logic of the database. For example, if we tried to delete the Class without removing ClassInstance records, there would be ClassInstance rows with ClassIDs that no longer exist. A properly designed database won’t let you do something like this.
  • One or more rows that you want to delete are in use - If another user or program is currently working on the records you’re trying to delete, SQL won’t let you delete them. Sometimes a single record being used will stop your entire delete statement since that record would be included.

Now we’ve mastered the three different kinds of changes we can make to data. We’re now going to take a break and talk about some of the alternatives to subqueries. We’ll learn about views, temporary tables and other tricks to help us organize our data.



author's photo

Author : SQL Tutor

Tainyan staff who teach SQL.

COMMENTS
blog comments powered by Disqus
Contributors
Categories