Inserting, Updating and Deleting Records
- To insert records into a table.
- To update records in a table.
- To delete records from a table.
Inserting new records into a table is not difficult. Dangerously, it is even easier to update and delete records.
INSERT
To insert a record into a table, you must specify values for all fields that do not have default values and cannot be NULL.
INSERT INTO table (columns) VALUES (values);
The second line of the above statement can be excluded if all required columns are inserted and the values are listed in the same order as the columns in the table. We recommend you include the second line all the time though as the code will be easier to read and update and less likely to break as the database is modified.
Code Sample: InsertsUpdatesDeletes/Demos/Insert.sql
-- Insert a New Employee
/******************************
Both of the inserts below will work in SQL Server
Oracle
******************************/
INSERT INTO Employees
(LastName, FirstName, Title, TitleOfCourtesy,
BirthDate, HireDate, Address, City, Region,
PostalCode, Country, HomePhone, Extension)
VALUES ('Dunn','Nat','Sales Representative','Mr.','19-Feb-1970',
'15-Jan-2004','4933 Jamesville Rd.','Jamesville','NY',
'13078','USA','315-555-5555','130');
/******************************
MySQL
******************************/
INSERT INTO Employees
(LastName, FirstName, Title, TitleOfCourtesy,
BirthDate, HireDate, Address, City, Region,
PostalCode, Country, HomePhone, Extension)
VALUES ('Dunn','Nat','Sales Representative','Mr.','1970-02-19',
'2004-01-15','4933 Jamesville Rd.','Jamesville','NY',
'13078','USA','315-555-5555','130');
If the INSERT is successful, the output will read something to this effect:
(1 row(s) affected)
Exercise: Inserting Records
In this exercise, you will practice inserting records.
-
Insert yourself into the Employees table.
- Include the following fields: LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, City, Region, PostalCode, Country, HomePhone, ReportsTo
- Insert an order for yourself in the Orders table.
- Include the following fields: CustomerID, EmployeeID, OrderDate, RequiredDate
- Insert order details in the Order_Details table.
- Include the following fields: OrderID, ProductID, UnitPrice, Quantity, Discount
UPDATE
The UPDATE statement allows you to update one or more fields for any number of records in a table. You must be very careful not to update more records than you intend to!
UPDATE table SET field = value, field = value, field = value WHERE conditions;
Code Sample: InsertsUpdatesDeletes/Demos/Update.sql
-- Update an Employee UPDATE Employees SET FirstName = 'Nathaniel' WHERE FirstName = 'Nat';
If the UPDATE is successful, the output will read something to this effect:
(1 row(s) affected)
DELETE
The DELETE statement allows you to delete one or more records in a table. Like with UPDATE, you must be very careful not to delete more records than you intend to!
DELETE FROM Employees WHERE conditions;
Code Sample: InsertsUpdatesDeletes/Demos/Delete.sql
-- Delete an Employee DELETE FROM Employees WHERE FirstName = 'Nathaniel';
If the DELETE is successful, the output will read something to this effect:
(1 row(s) affected)
Exercise: Updating and Deleting Records
In this exercise, you will practice updating and deleting records.
- Update your record in the Employees table to include some Notes.
- Raise the unit price of all products in the Products table by 10% for all products that are out of stock. This should affect 5 rows.
- Try to delete yourself from the Employees table. Could you?
- If you were not allowed to delete yourself from the Employees table, figure out what other records you have to delete so that you can.
Inserting, Updating and Deleting Records Conclusion
In this lesson of the SQL tutorial, you have learned how to insert, update, and delete records. Remember to be careful with updates and deletes. If you forget or mistype the WHERE clause, you could lose a lot of data.