Syntax error

[jurasekk]

Hi,
I'm having issues when making command to check MySQL integrity (to delete dead "links" after deleting things which are linked with another table)
Here is the code I made:
DELETE FROM `characters` LEFT JOIN `accounts` ON `characters`.`acct`=`accounts`.`acct` WHERE `accounts`.`acct`=NULL
I'm recieving this 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 'LEFT JOIN `accounts` ON `characters`.`acct`=`accounts`.`acct` WHERE `accounts`.`' at line 1"
http://img185.imageshack.us/img185/6576/picture2go3.th.png (http://img185.imageshack.us/my.php?image=picture2go3.png)
http://img185.imageshack.us/img185/3870/picture3it5.th.png (http://img185.imageshack.us/my.php?image=picture3it5.png)http://img185.imageshack.us/images/thpix.gif (http://g.imageshack.us/thpix.php)
http://img185.imageshack.us/img185/7346/picture4au3.th.png (http://img185.imageshack.us/my.php?image=picture4au3.png)http://img185.imageshack.us/images/thpix.gif (http://g.imageshack.us/thpix.php)
http://img185.imageshack.us/img185/8849/picture6dg2.th.png (http://img185.imageshack.us/my.php?image=picture6dg2.png)http://img185.imageshack.us/images/thpix.gif (http://g.imageshack.us/thpix.php)
http://img185.imageshack.us/img185/3527/picture6wc0.th.png (http://img185.imageshack.us/my.php?image=picture6wc0.png)http://img185.imageshack.us/images/thpix.gif (http://g.imageshack.us/thpix.php)

[NogDog]

Either:

DELETE `characters`, `accounts` FROM `characters`
LEFT JOIN `accounts` ON `characters`.`acct`=`accounts`.`acct`
WHERE `accounts`.`acct`=NULL

...or...

DELETE FROM `characters` LEFT JOIN `accounts` USING `acct`
WHERE `accounts`.`acct`=NULL

See "Multiple Table Syntax" on http://dev.mysql.com/doc/refman/5.0/en/delete.html.

[jurasekk]

Thanks for reply, no error but it's not working...

[NogDog]

Maybe you need to use WHERE IS NULL `accounts`.`acct` ?

[jurasekk]

Still not working... :(

[NogDog]

Is the `acct` field actually NULL, or just an empty string?

[jurasekk]

Delete if `acct` doesn't match

[chazzy]

what about something simpler


DELETE FROM `characters`
WHERE `characters`.`acct` NOT IN
select `acct` from `accounts`;

[jurasekk]

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 `acct` from `accounts`' at line 3

[chazzy]

sorry forgot parenthesis


DELETE FROM characters
WHERE acct NOT IN
(select acct from accounts);

[jurasekk]

Thank you very much, working. :)