mysql 4.1 win2000 SP3, UPDATE: bug or what?

[red85]

hello i have mysql 4.1 with win2000 SP3, i know that it is only an
alpha and i don't know if someone else has already posted this
problem:
when i execute this sql

UPDATE tableX SET fieldX=valueX
WHERE id IN (SELECT tX.id FROM tableX AS tX WHERE tX.fieldY=value Y AND
filedZ=valueZ);
(note that tableX and tableX are the same tables)

i have this error
"You can't specify target table 'tableX' for update in FROM clause"
while the same sql in access2000 works without problem.

do i have to use another syntax or is it a bug?

[red85]

execuse but i'm italian and i'm not so good in english...
you said i've to look in the manual, but 1.7.4.1 SubSELECTs is not in
the manual of mysql4.1alpha, it is in the one of mysql 4.0.13, and
i've got mysql4.1 aplha.
i only wonder why the sql with subselect

UPDATE tableX SET fieldX=valueX
WHERE id IN (SELECT tX.id FROM tableX AS tX WHERE tX.fieldY=value Y AND
filedZ=valueZ);

doesn't work while others sql with subselect, also very complicated,
works without any problem in mysql 4.1alpha

bye
ps excuse me if i've done a double post

"ybd" <w3**@yinboda.c om> wrote in message news:<bg******* ***@news.yaako. com>...

I think that you need read mysql manual at first~~~. Look at
-----------------
1.7.4.1 SubSELECTs

MySQL Server currently only supports nested queries of the form INSERT ...
SELECT ... and REPLACE ... SELECT .... You can, however, use the function
IN() in other contexts. Subselects are currently being implemented in the
4.1 development tree.

Meanwhile, you can often rewrite the query without a subselect:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be rewritten as:

SELECT table1.* FROM table1,table2 WHERE table1.id=table 2.id;
The queries:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table 2.id);
Can be rewritten as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table 2.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 subselects). For
this situation there are two options available until subqueries are
supported by MySQL Server.

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 construct a set of DELETE
statements automatically, using the MySQL extension CONCAT() (in lieu of the
standard || operator). For example:

SELECT CONCAT('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:

shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL Server 4.0 supports multi-table deletes that can be used to
efficiently delete rows based on information from one table or even from
many tables at the same time.

------------------------

Good Luck

"red85" <ho*******@inwi nd.it> ????
news:25******** *************** ***@posting.goo gle.com...

hello i have mysql 4.1 with win2000 SP3, i know that it is only an
alpha and i don't know if someone else has already posted this
problem:
when i execute this sql

UPDATE tableX SET fieldX=valueX
WHERE id IN (SELECT tX.id FROM tableX AS tX WHERE tX.fieldY=value Y AND
filedZ=valueZ);
(note that tableX and tableX are the same tables)

i have this error
"You can't specify target table 'tableX' for update in FROM clause"
while the same sql in access2000 works without problem.

do i have to use another syntax or is it a bug?

[red85]

execuse but i'm italian and i'm not so good in english...
you said i've to look in the manual, but 1.7.4.1 SubSELECTs is not in
the manual of mysql4.1alpha, it is in the one of mysql 4.0.13, and
i've got mysql4.1 aplha.
i only wonder why the sql with subselect

UPDATE tableX SET fieldX=valueX
WHERE id IN (SELECT tX.id FROM tableX AS tX WHERE tX.fieldY=value Y AND
filedZ=valueZ);

doesn't work while others sql with subselect, also very complicated,
works without any problem in mysql 4.1alpha

bye
ps excuse me if i've done a double post

"ybd" <w3**@yinboda.c om> wrote in message news:<bg******* ***@news.yaako. com>...

I think that you need read mysql manual at first~~~. Look at
-----------------
1.7.4.1 SubSELECTs

MySQL Server currently only supports nested queries of the form INSERT ...
SELECT ... and REPLACE ... SELECT .... You can, however, use the function
IN() in other contexts. Subselects are currently being implemented in the
4.1 development tree.

Meanwhile, you can often rewrite the query without a subselect:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be rewritten as:

SELECT table1.* FROM table1,table2 WHERE table1.id=table 2.id;
The queries:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table 2.id);
Can be rewritten as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table 2.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 subselects). For
this situation there are two options available until subqueries are
supported by MySQL Server.

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 construct a set of DELETE
statements automatically, using the MySQL extension CONCAT() (in lieu of the
standard || operator). For example:

SELECT CONCAT('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:

shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL Server 4.0 supports multi-table deletes that can be used to
efficiently delete rows based on information from one table or even from
many tables at the same time.

------------------------

Good Luck

"red85" <ho*******@inwi nd.it> ????
news:25******** *************** ***@posting.goo gle.com...

hello i have mysql 4.1 with win2000 SP3, i know that it is only an
alpha and i don't know if someone else has already posted this
problem:
when i execute this sql

UPDATE tableX SET fieldX=valueX
WHERE id IN (SELECT tX.id FROM tableX AS tX WHERE tX.fieldY=value Y AND
filedZ=valueZ);
(note that tableX and tableX are the same tables)

i have this error
"You can't specify target table 'tableX' for update in FROM clause"
while the same sql in access2000 works without problem.

do i have to use another syntax or is it a bug?

[red85]

UP!!

[red85]

UP!!

[red85]

UP!!