In continuation of our exploration of the DML (Data Manipulation Language) components of SQL (Structured Query Language), here we introduce two more fundamental commands - SQL DELETE and SQL UPDATE.
These two commands are used to edit the contents of the database, and do not alter the underlying structure of the database.
SecA_Bal
| Name | Customer# | Agent | Balance |
|---|---|---|---|
| John Smith | 2593 | Brown | 121.50 |
| Neale Fenn | 2529 | Brown | -42.00 |
| Kenny Perry | 2511 | Dowling | 553.50 |
| Jim Croker | 1311 | Brown | 0 |
DELETE is used in conjunction with WHERE, according to the following format:-
DELETE FROM table_name [WHERE search_condition]
Jim Croker has settled his account at last, and Agent Brown has happily informed us that we can expunge his details from our records. This is how it’s done.
DELETE FROM SecA_Bal WHERE Customer# = ‘1311’ ;
This will delete the entire row/record where Customer# is 1311. Note that we could have used the parameter WHERE Name = ‘Jim Croker’ in this instance. This would obviously not have been best practice, as a larger table may have another customer with that name. Customer# will be unique.
Here’s the result:-
SecA_Bal
| Name | Customer# | Agent | Balance |
|---|---|---|---|
| John Smith | 2593 | Brown | 121.50 |
| Neale Fenn | 2529 | Brown | -42.00 |
| Kenny Perry | 2511 | Dowling | 553.50 |
Should SecA_Bal become redundant for some reason or another, we can delete the entire table using the DELETE command. For this, we use the DELETE command with the table name without specifying any WHERE search condition.
DELETE FROM SecA_Bal ;
This deletes the entire table SecA_Bal.
UPDATE, like INSERT, DELETE and SELECT, is a fundamental DML command you must be comfortable with before progressing to learn the more general sub-functions of SQL.
UPDATE is used to modify existing data in the database. It is used in conjunction with SET [and WHERE, if necessary] according to the following format:-
Update table_name SET column_name = value [, column_name2 = value2, column_name3 = value3, …] [WHERE search_condition]
SecA_Bal
| Name | Customer# | Agent | Balance |
|---|---|---|---|
| John Smith | 2593 | Brown | 121.50 |
| Neale Fenn | 2529 | Brown | -42.00 |
| Kenny Perry | 2511 | Dowling | 553.50 |
| Jim Croker | 1311 | Brown | 0 |
Here’s a simple example. Agent Brown is all washed up, burned out and the company is giving him some leave. We’ll transfer all his customers to Dowling’s care for the moment.
UPDATE SecA_Bal SET AGENT = Dowling ;
This updates each cell of the AGENT column to Dowling. We didn’t need WHERE to stipulate conditions in this case, but most updates will require a WHERE clause.
It transpires that we have an incorrect name for Customer# 1311. Instead of Jim Croker, we have been asked to change his Name to Jim C. Rocker. The accounts department have also informed us that they have made a mistake, and that his Balance should in fact read -1.00.
Update SecA_Bal SET Name = ‘Jim C. Rocker’, Balance = -1.00 Where Customer# = 1311;
The company has decided to reward it’s customers with a discount. They wish to slice a piece off each customer’s outstanding balance, while of course ensuring that any customer currently with a positive balance is not rewarded. This update is done like this:-
UPDATE SecA_Bal SET Balance = Balance*0.90 WHERE Balance > 0 ;
Here’s the result of all these updates:-
| Name | Customer# | Agent | Balance |
|---|---|---|---|
| John Smith | 2593 | Dowling | 109.35 |
| Neale Fenn | 2529 | Dowling | -42.00 |
| Kenny Perry | 2511 | Dowling | 498.15 |
| Jim C. Roker | 1311 | Dowling | -1.00 |
In the next section we will consider the usage of the SELECT statement, a powerful and relatively complex DML command which is the basis of SQL queries.