SQL Tutorial - How to Update and Delete Records
In this article we look at the various methods on getting our data into our tables. Once it's there we also look at amending and finally deleting it. The following commands will be introduced, clicking an item below will skip directly to that section:
All of the examples in this artilce will use data from the following 'Employees' table:
| FirstName |
Surname |
JobTitle |
Age |
StartDate |
Salary |
| Andrew |
Jones |
Sales Executive |
28 |
10/11/2003 |
49816 |
| John |
Sullivan |
CEO |
44 |
12/10/1997 |
102000 |
| Keith |
Angler |
VP Development |
34 |
12/10/2001 |
72540 |
| Sarah |
Growler |
VP Development |
41 |
11/12/2006 |
81235 |
| Elizabeth |
Armitage |
Admin Assitant |
22 |
12/12/2008 |
29400 |
And an empty 'Employee_Archive' table with the structure:
| Column Name |
Data Type |
| FirstName |
char(50) |
| Surname |
char(50) |
| JobTitle |
char(100) |
The INSERT INTO command is the key to getting new data into your database tables, it is used in conjunction with the VALUES keyword to specify the table, columns and values to populate. The below example inserts a new record into the Employees table:
INSERT INTO
Employees(FirstName, Surname, JobTitle, Age, Salary)
VALUES
('Steven', 'Bloggs', 'Programmer', 31, 52500)
Although the column names to be populated are named above, this only needs to be done if not all of the columns are to to be filled. If they are all to be filled you can use the shortened version:
INSERT INTO
Employees
VALUES
('Steven', 'Bloggs', 'Programmer', 31, 52500)
The SELECT INTO command allows you to populate a table from records already stored in another table. This can be a useful way of copying data. The following example takes all of the records from the Employees table and inserts the key information into the Employee_Archive table:
SELECT
FirstName, Surname, JobTitle
INTO
Employee_Archive
FROM
Employees
Once we have populated our table with some data we may well need to change it, this can be done using the UPDATE and SET commands. The example belows changes all records with the surname 'Jones' to 'Smith':
UPDATE
Employees
SET
Surname = 'Smith'
WHERE
Surname = 'Jones'
If more than one field needs to be updated then the values can be separated by commas (,). If no WHERE clause is specified then the UPDATE will be applied to all records in the database. The query below updates the age and salary of all the employees in the table.
UPDATE
Employees
SET
Age = Age + 1, Salary = Salary * 1.05
Removing records from you database table is fairly straight-forward using the DELETE keyword:
DELETE FROM
Employees
WHERE
Surname = 'Jones'
You should be cautious when using the DELETE command as if no criteria is specified all the records within the table will be deleted, i.e.:
DELETE FROM
Employees