facebook google plus twitter
Webucator's Free SQL Tutorial

Lesson: Inserting, Updating and Deleting Records

Welcome to our free SQL tutorial. This tutorial is based on Webucator's Introduction to SQL Training course.

Inserting new records into a table is not difficult. Dangerously, it is even easier to update and delete records.

Lesson Goals

  • Learn to insert records into a table.
  • Learn to update records in a table.
  • Learn to delete records from a table.

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.

Syntax

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)

In the presentation that follows, the instructor states that the record ID is auto-generated by SQL when a record is inserted. While this is true for SQL Server and MySQL, it is not true for Oracle. In Oracle, an INSERT requires that the user specify a record ID. Therefore, this is an additional difference in addition to the date format between the three databases.

Inserting Records

Duration: 5 to 15 minutes.

In this exercise, you will practice inserting records.

  1. Insert yourself into the Employees table.
    • Include the following fields: LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, City, Region, PostalCode, Country, HomePhone, ReportsTo
  2. Insert an order for yourself in the Orders table.
    • Include the following fields: CustomerID, EmployeeID, OrderDate, RequiredDate
  3. Insert order details in the Order_Details table.
    • Include the following fields: OrderID, ProductID, UnitPrice, Quantity, Discount

Solution:

InsertsUpdatesDeletes/Solutions/Inserts.sql
/******************************
Oracle Solution
******************************/
INSERT INTO Employees
(EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, 
	BirthDate, HireDate, City, Region, 
	PostalCode, Country, HomePhone, ReportsTo)
VALUES(10, 'Dunn','Nat','Trainer','Mr.',
	'01-Feb-1970','04-Mar-1997','Jamesville','NY',
	'13078','USA','315-555-5555','1');

/******************************

SQL Server & MySQL Solution
******************************/
INSERT INTO Employees
(LastName, FirstName, Title, TitleOfCourtesy, 
	BirthDate, HireDate, City, Region, 
	PostalCode, Country, HomePhone, ReportsTo)
VALUES('Dunn','Nat','Trainer','Mr.',
	'1970-02-01','1997-03-04','Jamesville','NY',
	'13078','USA','315-555-5555','1');

/******************************

Oracle Solution
******************************/
INSERT INTO Orders
(OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate)
VALUES(11078, 'COMMI',10,'24-May-2004','12-Jul-2005');

/******************************
SQL Server & MySQL Solution
******************************/
INSERT INTO Orders
(CustomerID, EmployeeID, OrderDate, RequiredDate)
VALUES('COMMI',10,'2005-05-24','2005-07-12');

INSERT INTO Order_Details
(OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES(11078, 3, 10, 100, .1);

/*
	SQL Server users will replace Order_Details with "Order Details"

*/

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!

Syntax

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!

Syntax

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)

Updating and Deleting Records

Duration: 5 to 15 minutes.

In this exercise, you will practice updating and deleting records.

  1. Update your record in the Employees table to include some Notes.
  2. 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.
  3. Try to delete yourself from the Employees table. Could you?
  4. 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.

Solution:

InsertsUpdatesDeletes/Solutions/UpdateDelete.sql
UPDATE Employees
SET Notes = 'Nat does not really work here.'
WHERE FirstName = 'Nathaniel' AND LastName = 'Dunn';

UPDATE Products
SET UnitPrice = UnitPrice * 1.1
WHERE UnitsInStock = 0;

DELETE FROM "Order Details"
WHERE OrderID = 11078;
-- Note that your OrderID might be different

DELETE FROM Orders
WHERE OrderID = 11078;
-- Note that your OrderID might be different

DELETE FROM Employees
WHERE FirstName = 'Nathaniel' AND LastName = 'Dunn';