I have two tables : t1(a1 integer) and t2(a1 integer).
I am trying a dlete statement which works in Oracle but not on MySql
server.

delete from t2 t where t.a1 in (select b.a1 from t1 b where b.a1
between 2 and 5) ;

What is wrong with the syntax of that statement ?

On 5 mar, 09:59, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:
AFAIK your query is valid, but MySQL does not support alias in delete
statements (at least I could not find any support for it in the
documentation). Try:

delete from t2 where t2.a1 in ( select b.a1 from t1 as b where b.a1
between 2 and 5 );

/Lennart

In article <ef52f90a-9b3c-46bd-b5c7-acfa84e077ba (AT) g7g2000yqe (DOT) googlegroups.com>, Lennart <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
Or, much more simply,

DELETE FROM t2 WHERE a1 BETWEEN 2 AND 5;

Doug Miller wrote:
You're query will delete all rows in t2 where a1 is between 2 and 5.
The user only wants to delete the rows where a1 is between 2 and 5 AND
that value exists in t1.a1.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

dn.perl (AT) gmail (DOT) com wrote:
Sorry, my crystal ball is in the shop and I can't see what error message
you get.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

The MySql version is 4.0.26 ;
Each of the following command fails with Error 1064.
create table t1 (a1 integer) ;
create table t2 (a1 integer) ;


Command : delete from t2 where a1 in (select a1 from t1
where a1 between 2 and 5) ;
Message : You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'select a1 from t1 where a1 between 2 and 5)' at line 1

Command : delete from t2 where a1 in (select b.a1 from t1 b
where b.a1 between 2 and 5) ; Or use t2.a1 instead of just
a1
..
..
Command : delete from t2 a where a.a1 in (select b.a1 from t1 b
where b.a1 between 2 and 5) ;
Now the error-message changes in its content.
Message : You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'a where a.a1 in (select b.a1 from t1 b where b.a1 between

=====

In article <hmr05b$hpj$4 (AT) news (DOT) eternal-september.org>, Jerry Stuckle <jstucklex (AT) attglobal (DOT) net> wrote:

"dn.perl (AT) gmail (DOT) com" <dn.perl (AT) gmail (DOT) com> wrote:
And your computer is a 386?

Subqueries were added to MySQL in the 4.1 series.
Currently "recommended for production" is 5.1.


XL

On 5 Mar, 13:44, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:
This should be done with a NOT NULL test on a LEFT JOIN

On 10 mar, 14:12, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
I'm not sure I understand, can you provide an example?

/Lennart