The first option is to use a
procedural programming language (such as Perl or PHP) to submit a SELECT query
to obtain the primary keys for the records to be deleted, and then use these
values to construct the DELETE statement (DELETE FROM ... WHERE ... IN (key1,
key2, ...)).
The second option is to use
interactive SQL to contruct a set of DELETE statements automatically, using the
MySQL extension CONCAT() (in lieu of the standard || operator). For example:
SELECT CONCAT(The following will not
yet work in MySQL:
SELECT * FROM table1 WHERE id IN
(SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN
(SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS
(SELECT id FROM table2 where table1.id=table2.id);
However, in many cases you can rewrite
the query without a sub-select:
SELECT table1.* FROM table1,table2
WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN
table2 ON table1.id=table2.id where table2.id IS NULL
For more complicated subqueries you
can often create temporary tables to hold the subquery. In some cases, however
this option will not work. The most frequently encountered of these cases
arises with DELETE statements, for which standard SQL does not support joins
(except in sub-selects). For this situation there are two options available
until subqueries are supported by MySQL.
'DELETE FROM tab1 WHERE pkid = ',
tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place this query in a script
file and redirect input from it to the mysql command-line interpreter, piping
its output back to a second instance of the interpreter:
prompt> mysql --skip-column-names
mydb > myscript.sql | mysql mydb
MySQL only supports INSERT ... SELECT
... and REPLACE ... SELECT ... Independent sub-selects will probably be
available in Version 4.0. You can now use the function IN() in other contexts,
however.
MySQL - SELECT INTO TABLE
MySQL doesn't yet support the Oracle
SQL extension: SELECT ... INTO TABLE .... MySQL supports instead the ANSI SQL syntax
INSERT INTO ... SELECT ..., which is basically the same thing.
Alternatively, you can use SELECT INTO
OUTFILE... or CREATE TABLE ... SELECT to solve your problem.
No comments:
Post a Comment