Inserting, Updating, and Deleting Rows in SQL
INSERT Statement
The INSERT statement adds rows to a table.
- has two clauses:
INSERT INTOclause names the table and columns where data is to be addedVALUESclause 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
DEFAULTkeyword and default value follow the column name and data type in aCREATE TABLEstatement - Columns may be omitted from an
INSERTstatement- When omitted, a column is assigned a
NULLvalue - If the
NOT NULLconstraint is specified on the column, the insert is rejected
- When omitted, a column is assigned a
Update Statement
The UPDATE statement modifies existing rows in a table.
- uses the
SETclause to specify the new column values - optional
WHEREclause specifies which rows are updated- Omitting the
WHEREclause results in all rows being updated
- Omitting the
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.
FROMkeyword is followed by the table name whose rows are to be deleted- optional
WHEREclause 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
TRUNCATEstatement deletes all rows from a table.
- nearly identical to a
DELETEstatement with noWHEREclause except for minor differences that depend on the database system.- can’t be undone with other commands
DELETEcan- Syntax:
TRUNCATE TABLE TableName;