delete query won't do it's job! mysql version trouble??

[molly2ka]

Hi everyone,

I'm stuck on what should be a simplish delete query but goes nowhere. I am trying to delete rows from multiple tables in one query. I have mysql 4.0.13 installed on windows xp and according to the mysql manual it should support them. At the moment I am trying to delete from three tables.I'll describe the structure below:

table composition

comp_id(primary key)
(other fields)
####################
table movement

mov_id(primary key)
comp_id(foreign key from composition)
(other fields)
####################
table fragment

frag_id(primary key)
mov_id(foreign key)
(other fields0

ok so what I am trying to do is:

delete from composition c,movement m,fragment f where c.comp_id=m.comp_id AND m.mov_id =f.mov_id;

the where clause normally gives the correct output when used with select. When i try any multiple table deletes it just won't work. Can anyone please shed some light on this please???

ta,

manni

 

[TonyGroves]

Your query doesn't indicate what table(s) you're trying to delete from.

The syntax is:
[tt]DELETE t1,t2 FROM t1,t2,t3 WHERE ...[/tt]
or
[tt]DELETE FROM t1,t2 USING t1,t2,t3 WHERE ...[/tt]

 

[molly2ka]

i apologise i tried using delete * from.... Had no luck.

manni

 

[TonyGroves]

How does it not work? Is it that you get the wrong results (what happens?), or does the syntax get rejected?

 

[molly2ka]

it rejects the query. the only delete query it seems to accept is one for a single table

manni