INSERT, UPDATE and DELETE in SQL

Содержание

INSERT
UPDATE
DELETE

SQL uses INSERT, UPDATE, and DELETE statements to modify data.

INSERT

The INSERT statement inserts rows into a table.

It has two forms. The first allows you to insert strings explicitly specifying their value:

INSERT [INTO] tab_name [(col_list)]
DEFAULT VALUES | 
VALUES ({DEFAULT NULL expression} [ ,...n])[, (...n)]

INSERT [INTO] tab_name [(col_list)]

DEFAULT VALUES |

VALUES ({DEFAULT NULL expression} [ ,...n])[, (...n)]

For both forms, specifying a list of columns is optional. The DEFAULT VALUES parameter inserts default values for all columns.

The second form allows you to insert the result set of a SELECT statement or stored procedure into a table:

INSERT INTO tab_name | view_name [(col_list)]
{select_statement | execute_statement}

1 2	INSERT INTO tab_name \| view_name [(col_list)] {select_statement \| execute_statement}

UPDATE

The UPDATE statement lets to update table rows:

UPDATE tab_name
{SET column_1 = {expression | DEFAULT | NULL} [,...n]
[FROM tab_name1 [,...n]]
[WHERE condition]

UPDATE tab_name

{SET column_1 = {expression | DEFAULT | NULL} [,...n]

[FROM tab_name1 [,...n]]

[WHERE condition]

This query selects the rows of the tab_name table for modification according to the condition in the WHERE clause.

SET clause allows to change the column values of each modified row.

FROM allows you to select rows for modification based on a more complex condition using several linked pages.

DELETE

The DELETE statement removes rows from a table:

DELETE FROM table_name
[WHERE predicate];

1 2	DELETE FROM table_name [WHERE predicate];

All rows that satisfy the condition in the WHERE clause are removed. The WHERE clause can contain a subquery for a more complex condition, or you can use a slightly different form of the statement (the semantics is similar to the UPDATE statement):

DELETE table_name
FROM table_name [,...n]
[WHERE condition];

DELETE table_name

FROM table_name [,...n]

[WHERE condition];