INSERT

INSERT is used to create new rows in a table. It has this basic syntax:

Where:

1. table is a valid table in the schema.
2. column_name is a valid column name in table.
3. expression is a list of values which will be inserted into the column_name list. If column_name is omitted, expression must contain a valid value for each column in the table, in the natural order of the columns. Using DEFAULT instead of expression will insert the default value for that column instead.
4. query is a valid SQL query, the result of which matches the list of column_names (or the columns in the table if column_name is omitted.

For example, suppose we have a Fruits table with this structure:

And this table:

Colors

id	name
1	red
2	yellow

This statement will insert two rows into the fruits table, one for pear and one for cherry:

Since id was not specified in the column_name list, and there is a default value for id, the default will be entered.

Note that “auto-increment” is a description of what’s done rather than actual syntax. The actual syntax and implementation details vary from one RDBMS to another. For example, in SQL Server, setting the IDENTITY attribute on a field when creating it will cause the field to auto-increment. In MySQL and Oracle it’s AUTO_INCREMENT. In PostgreSQL it’s SERIAL.

We could do this instead, with the same result:

Since we make the VALUES list match the table structure, we can omit the column_name list.

This is not to be recommended in a production environment. For example, if we added another column with a default value to fruits, the previous INSERT statement would still run correctly, and this one would raise an error.

We can also insert the results of a SELECT query into a table. For example, assuming we have a fruits2 table with the same structure as fruits:

This will insert all the rows from fruits into fruits2, with default values for id.

UPDATE

UPDATE alters existing rows in a table. It has this basic syntax:

Let’s untangle that SET clause a bit. The clause supports these different syntaxes:
1. Columns and what they are to be set equal to can be listed one after the other, separated by commas (SET a = x, b = y, c = z).
2. All the columns can be listed first, followed by all the values to change them to (SET (a, b, c) = (x, y, z)).
3. A column or columns can be set equal to the result of a subquery (SET a = (SELECT x FROM table_name WHERE ...)).

In option 3, the result of the subquery must be a single row, and the columns in the result must match in number and type the columns in the list of columns to be changed.

With this table:

Family

id	name	age
1	Anne	65
2	Bob	62
3	John	61

Either of these two statements:

Will have this result:

id	name	age
1	Anne	65
2	Bobby	63
3	John	61

UPDATE also supports subqueries. With these two tables:

Candidates

id	name	votes
1	Lincoln	0
2	Roosevelt	0

Tally

id	cand_id
1	1
2	1
3	1
4	2
5	2
6	2

This statement:

Will have this result:

id	name	votes
1	Lincoln	3
2	Roosevelt	3

DELETE

DELETE removes existing rows from a table. It has this basic syntax:

If the WHERE clause is omitted, all rows in the table are deleted (the table is emptied). The WHERE clause has a similar function to that of the SELECT statement: condition evaluates to a boolean value, and all rows for which it evaluates to true are deleted.

With this table:

Family

id	name	age
1	Anne	65
2	Mary	52
3	Bob	63
4	John	61
5	David	60
6	Peter	59
7	Paul	57

This statement:

Then this SQL statement:

Will have this result:

id	name	age
1	Anne	65
3	Bob	63
4	John	61
5	David	60