delete and join

[Ruth]

My SQL version is 5.0.51 and  I have this query

 

DELETE FROM char_main, char_lair

USING char_main m, char_lair l

WHERE m.lair_id = l.id AND m.id = 9;

 

I get the following errors:

 

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 'char_main m, char_lair lWHERE m.lair_id = l.id AND m.id = 3' at line 1

 

So I changed it to read:

DELETE FROM char_main, char_lair

FROM char_main m, char_lair l

WHERE m.lair_id = l.id AND m.id = 9;

 

I get the following error:

 

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 'from where m.lair_id = l.id and m.id = 9

 

Can you help figure out whats wrong everything looks correct to me.

[Ruth]

I'm not sure if this would help but here is my create statement for both tables

 

char_main table:

CREATE TABLE char_main(

id INT(11) NOT NULL auto_increment,

alias VARCHAR(40) NOT NULL default '',

real_name VARCHAR(80) NOT NULL default '',

lair_id INT(11) NOT NULL default 0,

align enum('good', 'evil') NOT NULL default 'good',

PRIMARY KEY (id)

);

 

lair table:

CREATE TABLE char_lair(

id INT(11) NOT NULL auto_increment,

zip_id VARCHAR(10) NOT NULL default '00000',

lair_addr VARCHAR(40) NOT NULL default '',

PRIMARY KEY (id)

";

[fenway]

You can't have FROM twice! Drop the first one.

[Ruth]

What do you mean I have from twice it looks like I have one

[Ruth]

never mind I see it, thats what I get for not reading the entire code and then changing it. Now I'm getting the error unknown table char_main. What the jello the table is there why can't SQL see it. In fact SQL isn't able to see both tables

 

Here is the statment I changed to

 

delete char_main, char_lair

from char_main m, char_lair l

where m.lair_id = l.id and m.id = 9;

[fenway]

You sure you selected the correct DB?