Oracle 5.0, Reference Manual

Page: 1102 / 3232

SELECT
Syntax
1082
Currently, you cannot replace into a table and select from the same table in a subquery.
MySQL uses the following algorithm for
REPLACE
(and
LOAD DATA ... REPLACE
):
1. Try to insert the new row into the table
2. While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
a. Delete from the table the conflicting row that has the duplicate key value
b. Try again to insert the new row into the table
It is possible that in the case of a duplicate-key error, a storage engine may perform the
REPLACE
as
an update rather than a delete plus insert, but the semantics are the same. There are no user-visible
effects other than a possible difference in how the storage engine increments
Handler_xxx
status
variables.
13.2.8.
SELECT
Syntax
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition ]
[GROUP BY { col_name | expr | position }
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition ]
[ORDER BY { col_name | expr | position }
[ASC | DESC], ...]
[LIMIT {[ offset ,] row_count | row_count OFFSET offset }]
[PROCEDURE procedure_name ( argument_list )]
[INTO OUTFILE ' file_name ' export_options
| INTO DUMPFILE ' file_name '
| INTO var_name [, var_name ]]
[FOR UPDATE | LOCK IN SHARE MODE]]
SELECT
is used to retrieve rows selected from one or more tables, and can include
UNION
statements
and subqueries. See Section 13.2.8.4, “
UNION
Syntax” , and Section 13.2.9, “Subquery Syntax” .
The most commonly used clauses of
SELECT
statements are these:
• Each
select_expr
indicates a column that you want to retrieve. There must be at least one
select_expr
.
•
table_references
indicates the table or tables from which to retrieve rows. Its syntax is described
in Section 13.2.8.2, “
JOIN
Syntax” .
• The
WHERE
clause, if given, indicates the condition or conditions that rows must satisfy to be
selected.
where_condition
is an expression that evaluates to true for each row to be selected.
The statement selects all rows if there is no
WHERE
clause.
In the
WHERE
expression, you can use any of the functions and operators that MySQL supports,
except for aggregate (summary) functions. See Section 9.5, “Expression Syntax” , and Chapter 12,
Functions and Operators .
SELECT
can also be used to retrieve rows computed without reference to any table.
For example:
mysql> SELECT 1 + 1;
-> 2