SQL Delete
The delete statement is used to delete certain records from a table within the database. Be careful when
using this statement, because you really need to be sure what you are deleting. Many databases (Oracle for
instance) support a ROLLBACK statement to undo the effect the last given command, but there are also
a lot Database Management Systems that lack this feature. The structure of the DELETE statement is
as follows:
<delete-compontent>
<from-component>
[ <where-component> ]
Examples
DELETE FROM USERS
This statement will delete all the records stored within the users table, but not the table itself.
DELETE FROM USERS
WHERE USER_ID BETWEEN 6000 AND 6500
This statement will delete all the records stored within the users table with a unique user ID
between 6000 and 6500.
DELETE FROM USERS
WHERE USER_ID < 5000
This statement will delete all the records stored within the users table with a unique user ID
between less than 5000
DELETE FROM USERS
WHERE UPPER(USER_NAME) LIKE 'JOHN%'
This statement will delete all the records stored within the users table with a user name that starts
with the letters "John". The like statement is case sensitive, so the letters have to match "JOHN"
exactly. With a trick the WHERE clause was made case insensitive. Everything retrieved from
the USER_NAME column is changed to upper case so that "john", "John", "JOHN" and all other
possibilities will match the like.
DELETE FROM USERS
WHERE USER_ID > 9000 OR
USER_ID BETWEEN 6000 AND 6500
This statement will delete all the records stored within the users table with a unique user ID
between greater than 9000 or with a unique user ID between 6000 and 6500
Important note: These examples are just meant to give an indication what is possible
to do with SQL select statements. There is much more possible than is displayed here.
See Also
Contact the author: Send an electronic mail to: pajtroon@dds.nl.
Peter's ICQ Number is: #3900785.
This page: Copyright © 2002 Peter A. J. Troon
Note: This page is part of the Peter Troon Site.
|