Rows

INSERT

INSERT command to insert new rows into a table:

INSERT INTO table_name [(column-list)]
VALUES (list);

When you insert all table columns do not need to specify a list of columns, but then we put the value in the order that was specified with the CREATE TABLE command. Therefore, in order to become independent from the subsequent modification of the table it is safer to give a list of columns in the INSERT INTO clause.

Example

INSERT INTO dept (DEPTNO, DNAM, loc)
VALUES (50, 'SERVICE', 'NEW YORK');

In every INSERT command, you can insert only one row.

The INSERT SQL functions are allowed on individual rows, but do not use subqueries.

Insert rows selected in the subquery

We can put the table rows that are the result of a SELECT query.

INSERT INTO table-name [-list]
SELECT expr-list
FROM ...

In this way we can with a single INSERT command to insert multiple rows.

Example

To copy a table MANAGER all employees working in a MANAGER write:

INSERT INTO manager
SELECT (empno, ename, sal, job, HireDate)
FROM emp
WHERE job = 'MANAGER';

Modify the lines

UPDATE

To change the contents of the rows, use the command UPDATE.

UPDATE table-name [alias]
SET column = {expression | subquery}
[, Column = {expression | subquery} ...]
[WHERE condition];

Example

UPDATE dept
DNAM SET = 'MARKETING'
loc = 'DENVER'
WHERE DEPTNO = 20;

If you do not type in the WHERE clause, it will be modified all the rows of the table.

The UPDATE command can use subqueries, both nested and correlated.

Deleting Rows

The DELETE command

To delete rows from a table, use the command DELETE.
DELETE [FROM] table
[WHERE condition];

The condition in the WHERE clause specifies which rows will be deleted.

Example

DELETE FROM emp
WHERE job = 'MANAGER';

Will be removed prasownicy working as a MANAGER.