Data Manipulation Statements

Part:   1   2 

This chapter describes data manipulation statements:

• Insert Statements
• Update Statements
• Delete Statements

Insert Statements

An insert statement allows you to insert new rows of data into an existing table. It has a number of syntax formats:

1. To insert a single row of all columns with values resulting from the specified expressions:

INSERT INTO tbl_name VALUES (expression, expression, ...);

When executed, all expressions will be evaluated, and the resulting values will form the new row, which will be inserted into the specified table. Of course, the number of expressions must be equal to the number of columns in table.

2. To insert a single row with some columns having specified values, and others having default values:

INSERT INTO tbl_name (column, column, ...) VALUES (expression, 
   expression, ...);

Notes:

• Obviously, duplicated columns are not allowed in the column list.
• The number of expressions must be equal to the number of specified columns.
• Default values will be provided for those columns that are not specified in the column list.

3. To insert one or more rows of all columns with a select sub-statement:

INSERT INTO tbl_name select_statement;

When executed, the output rows of the select sub-statement will be inserted into the specified table. Of course, the number of select expressions in the select statement must be equal to the number of columns of the specified table.

4. To insert one or more rows with some column having values from the specified select sub-statement, and other columns having default values:

INSERT INTO tbl_name (column, column, ...) select_statement;

Notes:

• Obviously, duplicated columns are not allowed in the column list.
• The number of select expressions must be equal to the number of specified columns.
• Default values will be provided for those columns that are not specified in the column list.

Here is an example SQL code, InsertRows.sql, showing you how to insert rows into an existing table:

-- InsertRows.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8));
INSERT INTO User VALUES ('herong','8IS3KOXW');
INSERT INTO User (Login) VALUES ('mike');
INSERT INTO User SELECT Login, Password FROM User;
INSERT INTO User (Password) SELECT CONCAT('__',Login) FROM User;
SELECT 'User table:' AS '---';
SELECT * FROM User;

(Continued on next part...)

Part:   1   2