What is the DELETE Keyword?
The SQL DELETE command is used to delete rows that are no longer required from the database tables. It deletes the whole row from the table. Delete command comes in handy to delete temporary or obsolete data from your database.The DELETE command can delete more than one row from a table in a single query. This proves to be advantages when removing large numbers of rows from a database table.
Once a row has been deleted, it cannot be recovered. It is therefore strongly recommended to make database backups before deleting any data from the database. This can allow you to restore the database and view the data later on should it be required.
Delete command syntax
The basic syntax of the delete command is as shown below.
DELETE FROM `table_name` [WHERE condition];
HERE
- DELETE FROM `table_name` tells MySQL server to remove rows from the table ..
- [WHERE condition] is optional and is used to put a filter that restricts the number of rows affected by the DELETE query.
If the WHERE clause is not used in the DELETE query, then all the rows in a given table will be deleted. Before we go into more details discussion the DELETE command, let's insert some sample data into the movies table to work with.
INSERT INTO `movies` (`title`, `director`, `year_released`, `category_id`) VALUES ('The Great Dictator', 'Chalie Chaplie', 1920, 7);
INSERT INTO `movies` (`title`, `director`, `category_id`) VALUES ('sample movie', 'Anonymous', 8);
INSERT INTO movies (`title`, `director`, `year_released`, `category_id`) VALUES ('movie 3', 'John Brown', 1920, 8);
Executing the above script adds three (3) movies into the movies table. Before we go any further into our lesson, let's get all the movies in our table. The script shown below does that.
SELECT * FROM `movies`;
Executing the above script gives us the following results.
movie_id itle director year_released category_id 1 Pirates of the Caribean 4 Rob Marshall 2011 1 2 Forgetting Sarah Marshal Nicholas Stoller 2008 2 3 X-Men NULL 2008 NULL 4 Code Name Black Edgar Jimz 2010 NULL 5 Daddy's Little Girls NULL 2007 8 6 Angels and Demons NULL 2007 6 7 Davinci Code NULL 2007 6 9 Honey mooners John Schultz 2005 8 16 67% Guilty NULL 2012 NULL 18 The Great Dictator Chalie Chaplie 1920 7 19 sample movie Anonymous NULL 8 20 movie 3 John Brown 1920 8
Let's suppose that the Myflix video library no longer wishes to be renting out "The Great Dictator" to its members and they want it removed from the database. Its movie id is 18, we can use the script shown below to delete its row from the movies table.
DELETE FROM `movies` WHERE `movie_id` = 18;
Executing the above script in MySQL WorkBench against the Myflix deletes the movie with id 18 from the database table.
Let's see the current status of movies table.
SELECT * FROM `movies`;
movie_id title director year_released category_id 1 Pirates of the Caribean 4 Rob Marshall 2011 1 2 Forgetting Sarah Marshal Nicholas Stoller 2008 2 3 X-Men NULL 2008 NULL 4 Code Name Black Edgar Jimz 2010 NULL 5 Daddy's Little Girls NULL 2007 8 6 Angels and Demons NULL 2007 6 7 Davinci Code NULL 2007 6 9 Honey mooners John Schultz 2005 8 16 67% Guilty NULL 2012 NULL 19 sample movie Anonymous NULL 8 20 movie 3 John Brown 1920 8
NOTE:
- the movie with id 18 has not been return in the query result set.
- you cannot delete a single column for a table. You can delete an entire row.
Let's say we have a list of movies we want to delete . We can use the WHERE clause along with IN.
DELETE FROM `movies` WHERE `movie_id` IN (20,21);
Executing the above script deletes movies with IDs 20 and 21 from our movies table.
WHAT IS THE UPDATE COMMAND?
The Update command is used to modify rows in a table. The update command can be used to update a single field or multiple fields at the same time. It can also be used to update a table with values from another table .
Update command syntax
The basic syntax of the SQL Update command is as shown below.
UPDATE `table_name` SET `column_name` = `new_value' [WHERE condition];
HERE
- UPDATE `table_name` is the command that tells MySQL to update the data in a table .
- SET `column_name` = `new_value' are the names and values of the fields to be affected by the update query. Note, when setting the update values, strings data types must be in single quotes. Numeric values do not need to be in quotation marks. Date data type must be in single quotes and in the format 'YYYY-MM-DD'.
- [WHERE condition] is optional and can be used to put a filter that restricts the number of rows affected by the DELETE query.
Let's now look at a practical example that updates data in the members table. Let's suppose that our member's membership numbers 1 and 2 have the following updates to be made to their data records.
| Membership number | Updates required |
| 1 | Changed contact number from 999 to 0759 253 532 |
| 2 | Change the name to Janet Smith Jones and physical address should be updated to Melrose 123 |
We will start with making updates for membership number 1 before we make any updates to our data, let's retrieve the record for membership number 1. The script shown below helps us to do that.
SELECT * FROM `members` WHERE `membership_number` = 1;
Executing the above script gives us the following results.
membership_number full_names gender date_of_birth physical_address postal_address contct_number 1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 999 janetjones@yagoo.cm
Let's now update the contact number using the script shown below.
UPDATE `members` SET `contact_number` = '0759 253 542' WHERE `membership_number` = 1;
Executing the above script updates the contact number from 999 to 0759 253 532 for membership number 1. Let's now look at the record for membership number 1 after executing the update script.
SELECT * FROM `members` WHERE `membership_number` = 1;
Executing the above script gives us the following results.
membership_number full_names gender date_of_birth physical_address postal_address contct_number 1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 janetjones@yagoo.cm
Let's now look at the updates required for membership number 2.
| membership_number | full_names | gender | date_of_birth | physical_address | postal_address | contct_number | |
|---|---|---|---|---|---|---|---|
| 2 | Smith Jones | Female | 23-06-1980 | Park Street | NULL | NULL | jj@fstreet.com |
The following script helps us to do that.
UPDATE `members` SET `full_names` = 'Janet Smith Jones', `physical_address` = 'Melrose 123' WHERE `membership_number` = 2;
Executing the above script in updates the full names for membership number 2 to Janet Smith Jones and the physical address to Melrose 123.
membership_number full_names gender date_of_birth physical_address postal_address contct_number 2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL jj@fstreet.com
Summary
- The delete command is used to remove data that is no longer required from a table.
- The "WHERE clause" is used to limit the number of rows affected by the DELETE query.
- Once data has been deleted, it cannot be recovered, it is therefore strongly recommend make backups before deleting data.
- The update command is used to modify existing data.
- The "WHERE clause" is used to limit the number of rows affected by the UPDATE query.
.jpg)
0 comments:
Post a Comment