Inserting, Updating, and Deleting Rows in SQL


INSERT Statement

The INSERT statement adds rows to a table.

  • has two clauses:
    • INSERT INTO clause names the table and columns where data is to be added
    • VALUES clause specifies the column values to be added
      • may list any number of rows in parentheses to insert multiple rows

Syntax

INSERT [INTO] TableName (Column1, Column2, ...)
VALUES (Value1, Value2, ...);

Example

INSERT INTO Employee (ID, Name, Salary)
VALUES (2538, 'Lisa Ellison', 45000);

Default Values

A default value may be specified for a column.

  • optional DEFAULT keyword and default value follow the column name and data type in a CREATE TABLE statement
  • Columns may be omitted from an INSERT statement
    • When omitted, a column is assigned a NULL value
    • If the NOT NULL constraint is specified on the column, the insert is rejected

Update Statement

The UPDATE statement modifies existing rows in a table.

  • uses the SET clause to specify the new column values
  • optional WHERE clause specifies which rows are updated
    • Omitting the WHERE clause results in all rows being updated

Syntax

UPDATE TableName
SET Column1 = Value1, Column2 = Value2, ...
WHERE condition;

Example

UPDATE Employee
SET Name = 'Tom Snead',
  BirthDate = '2000-03-15'
Where ID = 5384;

Delete Statement

The DELETE statement deletes existing rows in a table.

  • FROM keyword is followed by the table name whose rows are to be deleted
  • optional WHERE clause specifies which rows should be deleted
    • Omitting results in all rows in the table being deleted

Syntax

DELETE FROM TableName 
WHERE condition;

Truncate Statement

The TRUNCATE statement deletes all rows from a table.

  • nearly identical to a DELETE statement with no WHERE clause except for minor differences that depend on the database system.
  • can’t be undone with other commands
    • DELETE can
  • Syntax: TRUNCATE TABLE TableName;