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)
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)
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.