Data manipulation language (DML)

The Data Manipulation Language is the part of the SQL language used to perform operations on the content (ROWS) of one or more tables.

There are only four possible operations: the insertion of rows, the withdrawal of rows, the interrogation of rows and changing the values of one or more columns.

Each of these operations corresponds to only one SQL statement. So, there are only four instructions in SQL to manipulate the data! (The INSERT, SELECT, UPDATE, DELETE, and statements).

In the programming world these operations are often used as CRUD abbreviation, standing for: Create, Read, Update, Delete.

Additions and withdrawals are performed on entire rows (i.e., all columns in the row). On the other hand, the interrogation or modification operations can be carried out on certain columns only.

Note that only the query operation can be performed on multiple tables simultaneously. All others are done on only one table at a time.

This section focuses on single-table queries.
Multi-table queries (named joins) are discussed in the next section.

Adding rows to a table (INSERT)

Add a single new row

The INSERT statement is used to add a row to a table.

INSERT INTO table [(list of columns)]
VALUES (list of values);

The list of columns is optional, but becomes mandatory when:

The list of values does not respect the order of the columns present in the table, and or inserts fewer values than there are columns in the table.

Note:

• The data types between the provided values and the columns of the table must be compatible.
• You can replace a missing value with the NULL keyword.

Add an employee named John Smith whose number is 357.

INSERT INTO Employee
VALUES (357, 'John Smith', NULL);

or

INSERT INTO Employee (number, name)
VALUES (357, 'John Smith');

Add multiple rows from an existing table

Multiple rows can be added to a table in a single statement, replacing the VALUES clause with a subquery (see the appropriate section).

INSERT INTO table [(list of columns)]
subquery;

Of course, this instruction requires that the rows to be added to the table already exist in another table. Your subquery must produce a result whose columns are compatible with those in the table.

Deleting rows in a table (DELETE)

The DELETE statement is used to delete one or more rows in a table. All rows that meet the condition (s) are erased. The table can become completely empty of rows but remains as a structure of the database.

DELETE FROM table
[WHERE conditional expression];

Since this instruction is for existing lines in the table, we must be able to specify which row(s) we are interested in. To do this, one must write a conditional expression (see the appropriate section).

Note:

• If no conditions are set, then all rows are erased.
• If an equality condition is set on the primary key, then only one row is erased.
• If any other condition is set, then more than one row is erased depending on the result of the condition check for each row encountered in the table.

Delete inventory parts that belong to the W67 type.

DELETE FROM product
WHERE type = 'W67';

Editing rows in a table (UPDATE)

The UPDATE statement is used to modify one or more existing values in a table.

UPDATE table
SET column = {value | subquery}
[WHERE conditional expression];

Attention, the symbol “=” is not here a symbol of equality, but rather represents an
assignment. It is therefore important that the column is on the left and the value assigned on the right. It is also possible to include the column in the value expression, which means that the old value of the column is used to modify it.

Increase the cost of 384R by 10%.

UPDATE product
SET cost = cost * 1.1
WHERE product_code = '384R';

If multiple columns need to be modified, then the SET clause contains a comma separated list of changes. Note that there is always only one SET clause.

Increase the cost of 384R by 10% and increase the amount by 2.

UPDATE product
SET cost = cost * 1.1, quantity = quantity + 2
WHERE product_code = '384R';

If a subquery is used to provide the value to be assigned, make sure that the subquery produces only one column and one row. This restriction sometimes makes it difficult to use a subquery.

Move to the next article: Query tables with SELECT