You know how to create tables, how to create records in the table, how to select and filter them and how to update them. In this lesson, you will see how to delete existing records from a table. To delete the existing records, the DELETE query is used.
Note: Before executing the following queries you must have records in the products and categories tables as mentioned in lesson 2 and lesson 3. If you do not have records in the products and categories table, go back and insert the records and then execute the following queries.
The DELETE Query
The DELETE query is very straight forward. You have to specify DELETE keyword followed by FROM keyword and the name of the table that you want to delete records from. For instance, the following query will DELETE all the records from the products table.
DELETE FROM products
If you execute the above query all the records will be deleted from the products table.
Note: Before executing the next Queries, make sure that you have reinserted all the records in the product table. You can do so with the following query:
INSERT INTO products VALUES (null, 'Samsung Galaxy 9', 700, 'yes', 2), (null, 'Core i7', 800, 'yes', 1), (null, 'Core i3' , 400, 'no', 1), (null, 'Apple Iphone X', 800, 'yes', 2), (null, 'Cannon super short', 350, 'yes', 3), (null, 'Sonny Headphones', 100, 'no', 4), (null, 'Core i5', 600, 'yes', 1), (null, 'Konica Minolta', 500, 'no', 4), (null, 'Samsung Galaxy 10', 900, 'yes', 2), (null, 'Mic Samsung', 50, 'yes', 4)
You can also delete specific records using a DELETE query. For instance, the following query deletes the product named “Apple Iphone X”:
DELETE FROM products WHERE name = "Apple Iphone X"
Now if you select the records from products table using the SELECT query, you should not see “Apple Iphone X” in the list of products as shown below:
From the above output, you can see that the record with id 4 i.e. “Apple Iphone X” has been deleted.
In this lesson, you saw how to delete records. In the next lesson you will see how to change the order in which records are retrieved.