labs / tiddlers / content / labs / lab04 / _Labs_04_Delete

CAUTION! It’s very easy to delete more data than you intended if you’re not careful. Make sure you save your INSERT statements from Task 1.2 so that you can re-create any data that you delete during this task.

Sometimes you also need to remove data from a table. You can do this using SQL’s DELETE statement, which has the following general form:

delete from <table name>
[where <condition>];

For example, the following will delete the Scientist with the ID 12345:

delete from Scientist
where Scientist_Num = '12345';

Check the contents of your Scientist table, run the above DELETE statement, then check the table contents again. (Remember that you may need to modify column names and values to be compatible with your table.) Have the contents of the table changed, and does this match what you expected to happen? If not, why not?

The WHERE clause is absolutely critical for most delete operations. Only rows that match the specified criteria will be deleted. What will happen if you run the statement delete from Scientist?

Deleting data isn’t always this straightforward, though. Run the three INSERT statements below, modifying column names and values as necessary to match your tables. (Hint: To run multiple statements, select all of them in the SQL worksheet, then click the Run Script button, which is second from the left in the SQL Worksheet toolbar, and looks like a document with a green triangle on top of it. The selected statements will run in sequence.)

insert into Scientist (Scientist_Num, Surname, Other_Names, Email, Mobile_Phone)
values ('54321', 'Jones', 'Heather', '', '+64 27 297 1619');

insert into Site (Site_ID, Region, Description, Latitude, Longitude,
                  Catchment_Area, Catchment_Height, Altitude)
values ('DN20', 'Dunedin', 'Leith at Clyde St. Br.', -45.867093, 170.516081,
        null, null, null);

insert into Sample (Scientist_Num, Site_ID, Recorded_On, Comments)
values ('54321', 'DN20', to_date('25/11/2016', 'DD/MM/YYYY'), null);

Now run the following statement:

delete from Site
where Site_ID = 'DN20';

What happens, and why?

How can you work around this?

After all that inserting and deleting, your data may have become a little messy. It wouldn’t hurt at this point to delete everything from all three tables and rebuild the data from scratch using the INSERT statements that you saved earlier. (You did save them, didn’t you? ) Just delete the data, don’t drop the tables.