Advanced Database – Create, run a query to delete records in a table.

Imagine we wanted to delete the last 3 jobs from the ‘Jobs’ table as the customers cancelled last minute.

We could go through and manually delete them, but imagine if we had hundreds of records to delete (maybe we archived them somewhere else). This would take a long time.

10 Delete Records Table

We can use the DELETE SQL command instead. Select Tools->SQL…

11 SQL Statement Delete

We want to delete records 19,20,21 from the Jobs table.

Type: DELETE FROM "Jobs" WHERE "Job No">18

This means it will delete from the selected table ‘Jobs’ all of the records that have a Job No greater than 18, i.e. 19,20,21

Click execute to run the query.

12 Delete executed table

Close the SQL window and open the Jobs table.

Notice that records containing job numbers 19, 20 and 21 have now been deleted.

Let’s say we wanted to delete record 10, we could just use the statement: DELETE FROM "Jobs" WHERE "Job No“=10

If we want to delete records less than or equal to 6, use: DELETE FROM "Jobs" WHERE "Job No“<=6 This would delete rows 1,2,3,4,5 and 6

In general, to delete records from a table use the SQL syntax:

DELETE FROM name_table
WHERE some_condition