Rows are DELETEd from the specified table when the condition statement is met.

DELETE all rows:             DELETE FROM table;

or

DELETE one or more rows:     DELETE FROM table
                              WHERE conditions;

> It is often convenient to test the WHERE clause with a SELECT statement to make sure the proper rows will be selected before using it in a DELETE.
> A subselect may be used to retrieve values for the WHERE clause.
> A subselect is a SELECT statement as described previously that has been attached to another SQL command.

DELETE removes rows from a table.
The optional WHERE clause specifies which rows to delete.

               DELETE FROM table
               [WHERE conditions];

> DELETE removes rows from a table, but it does not delete the table’s definition.
Even if you delete all the rows in a table, the table still exists, but it is empty.
> DELETE has no column names, because it removes the entire row.
> DELETE without a WHERE clause deletes all the rows in the table. For this reason,
a delete without a where clause can be dangerous, because it is easy to do.
> Since the order of the rows is out of your control and is unimportant,
DELETE may reorder the rows. See ORDER BY to reorder your SELECTed data.
> To preserve referential integrity, the table definition can contain the action to be
taken when a row is deleted that has a FOREIGN KEY reference.

 

UPDATE does not add or delete rows, it only updates row data.
UPDATE without a WHERE clause will update all rows.
For this reason, you must be careful when doing an update.
The WHERE clause specifies the rows to be updated.

     UPDATE table
        SET column1 = [value,
                     | hostvariable,
                     | expression,
                     | NULL,
                     | specialregister,]
           [column2 = [value, ...
                     | hostvariable, ...
                     | expression, ...
                     | NULL, ...
                     | specialregister, ...]]
    [WHERE conditions];

> The SET value or expression must match the data type of the column and
replaces the existing value in that column.
> An expression can not cause an arithmetic error such as divide by zero.
> A value can not violate a CHECK constraint or a FOREIGN KEY constraint.

 

To INSERT multiple rows into a table, the column names must be specified or the field values must be in exactly the same order as the columns are defined in the table.
All notes in the One Row topic apply to Multiple Rows.

Positional Insert:     INSERT INTO table
                              subselect;

This command copies all rows and column values from the subselect to the INSERT table.

                                                                       or

Named Columns:          INSERT INTO table
                               (column1, column2, column3, ...)
                               subselect;

This command copies column values from the subselect to the INSERT table.

> The process of adding data to a new table is called “populating” the table.
> A subselect is a SELECT statement as described previously that has been attached to another SQL command.
> When column names are specified, SQL ignores the column names in the subselect and uses column position to match with the column names specified in the INSERT table.
> An omitted column is given its default value or null.
> The table in the subselect remains unchanged.
> You can also INSERT rows through a VIEW.
> With some RDBMS systems, the INTO keyword is optional. But you should include the word INTO for portability.
> The SQL2 standard allows UNION and JOIN in the subselect.

 

 

To INSERT a single row into a table, the column names must be specified, or the field values must be in the same exact order as the columns are defined in the table.

Positional INSERT:   INSERT INTO table
                     VALUES (value1, value2, NULL, ...);

                                                                      or

Named Columns:       INSERT INTO table
                            (column1, column2, column3, ...)
                     VALUES (value1,  value2,  NULL,    ...);

> The columns not specified in the positional INSERT must be created as NULL or NOT NULL with DEFAULT.
> The values specified must be compatible with the respective column’s data type or be convertible to that data type.
> Character and datetime data types must be enclosed by quotation marks.
> Numeric data and the keyword NULL must not be enclosed with quotation marks.
> A value of two single quotes (”) with no spaces between the quotes also symbolizes a NULL.
> These statements do not return a result, but your RDBMS will normally return a message showing that the statement executed successfully and it should show the number of rows affected by the change.
> An inserted value can not violate a CHECK constraint. See Check Constraint.
> An inserted foreign key value must contain NULL (if it is allowed) or an existing key value from the primary or unique key referenced by the foreign key. See Primary Keys and Foreign Keys.

 

Every database starts out empty.
You must first create the table definition using SQL’s Data Definition Language, which we will cover in a future section.
When you have defined the table, it still contains no data.
You must INSERT data into the table to make it useful.
The INSERT statement adds one or more rows to a table.

      INSERT INTO table
           [(COLUMN1, COLUMN2, ...)]
     [VALUES ([value1, value2, ...
            | hostvariable, ...
            | expression, ...
            |NULL, ...
            | specialregister, ...)
            | subselect];

> This is the only way to load data into tables per the SQL standard.
However, many vendors have their own tools for loading tables.
For example, Oracle has a utility called SQL *Loader.

Go on and we will cover the details.

 

To get you started as quickly as possible, we will discuss INSERT, UPDATE, and DELETE before we get into the specifics of data types and functions.To INSERT, UPDATE, or DELETE data, you must know about the columns in the table:

> What are the column names?
> What is the order of the columns in the table?
> What is the data type of each column?
> Must the column’s value be unique within that column?
> Does each column have a default value?
> Can the column’s value be NULL?
> Is the column a key or part of a key?
> Remember that the order of rows are unimportant and you have no control over the physical location of rows. ORDER BY can be used to set the sequence of the rows retrieved.
> If you are executing transactions, you must use a COMMIT after your last INSERT, UPDATE, or DELETE to make the changes permanent.
> On your RDBMS system, to see the actual column titles, sequence, and values use a SELECT * FROM table, where “table” is the table name.

Note: To see the table and column definitions used in this course, in any EXAMPLE or LAB, click on the TABLE button, click on the table name you wish to see, and then click on the DEFINED button to see the table definition.