Oracle 5.0, Reference Manual

Page: 1087 / 3232

INSERT
Syntax
1067
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
But the following is not legal, because the value for
col1
refers to
col2
, which is assigned after
col1
:
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
One exception involves columns that contain
AUTO_INCREMENT
values. Because the
AUTO_INCREMENT
value is generated after other value assignments, any reference to an
AUTO_INCREMENT
column in the assignment returns a
0
.
INSERT
statements that use
VALUES
syntax can insert multiple rows. To do this, include multiple lists
of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The values list for each row must be enclosed within parentheses. The following statement is illegal
because the number of values in the list does not match the number of column names:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
VALUE
is a synonym for
VALUES
in this context. Neither implies anything about the number of values
lists, and either may be used whether there is a single values list or multiple lists.
The affected-rows value for an
INSERT
can be obtained using the
ROW_COUNT()
[963] function
(see Section 12.13, “Information Functions” ), or the
mysql_affected_rows()
C API function (see
Section 20.6.6.1, “
mysql_affected_rows()
” ).
If you use an
INSERT ... VALUES
statement with multiple value lists or
INSERT ... SELECT
, the
statement returns an information string in this format:
Records: 100 Duplicates: 0 Warnings: 0
Records
indicates the number of rows processed by the statement. (This is not necessarily the
number of rows actually inserted because
Duplicates
can be nonzero.)
Duplicates
indicates the
number of rows that could not be inserted because they would duplicate some existing unique index
value.
Warnings
indicates the number of attempts to insert column values that were problematic in
some way. Warnings can occur under any of the following conditions:
• Inserting
NULL
into a column that has been declared
NOT NULL
. For multiple-row
INSERT
statements or
INSERT INTO ... SELECT
statements, the column is set to the implicit default
value for the column data type. This is
0
for numeric types, the empty string (
''
) for string types,
and the “zero” value for date and time types.
INSERT INTO ... SELECT
statements are handled
the same way as multiple-row inserts because the server does not examine the result set from the
SELECT
to see whether it returns a single row. (For a single-row
INSERT
, no warning occurs when
NULL
is inserted into a
NOT NULL
column. Instead, the statement fails with an error.)
• Setting a numeric column to a value that lies outside the column's range. The value is clipped to the
closest endpoint of the range.
• Assigning a value such as
'10.34 a'
to a numeric column. The trailing nonnumeric text is stripped
off and the remaining numeric part is inserted. If the string value has no leading numeric part, the
column is set to
0
.
• Inserting a string into a string column (
CHAR
,
VARCHAR
,
TEXT
, or
BLOB
) that exceeds the column's
maximum length. The value is truncated to the column's maximum length.
• Inserting a value into a date or time column that is illegal for the data type. The column is set to the
appropriate zero value for the type.
If you are using the C API, the information string can be obtained by invoking the
mysql_info()
function. See Section 20.6.6.35, “
mysql_info()
” .