Database Reference
In-Depth Information
15.5.1
Deleting One Row
Using the WHERE clause, you can specify one row when deleting. In the
TESTMUSICCD table, delete the row for the “C'mon, C'mon” CD by
typing this DELETE command:
DELETE FROM TESTMUSICCD
WHERE TITLE = 'C''mon, C''mon';
SQL*Plus Worksheet will reply: “1 row deleted.”
Notice the use of quotation marks in the title. The title has two single
quotes in it where the data actually has a single quote. This is called a string
escape sequence. Because Oracle Database 10
g
uses single quote marks to
delimit literal values, you must indicate that the single quote in the middle
is not a delimiter by typing two single quote marks together. Remember
that two single quotes are not the same as one double quotation mark.
15.5.2
Deleting Many Rows
Just like the UPDATE command, simply revising the WHERE clause to
select more rows enables you to delete multiple rows in one command. For
example, deleting all CDs by the Goo Goo Dolls can be accomplished
using the following command:
DELETE FROM TESTMUSICCD
WHERE ARTIST_NAME = 'Goo Goo Dolls';
SQL*Plus Worksheet will reply: “2 rows deleted.”
The following query will show that rows for the Goo Goo Dolls and for
the CD named “C'mon, C'mon” are no longer in the table. Figure 15.15
shows the result.
SELECT * FROM TESTMUSICCD;
If a table is the parent of another table, such as the MUSICCD table,
which is the parent to the CDTRACK table, you cannot delete a row in the
MUSICCD table that has related child rows (CD tracks) in the
CDTRACK table. You should remove the child rows first and the parent
row last.
