Click to See Complete Forum and Search --> : Syntax error


jurasekk
10-14-2008, 11:31 AM
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
10-14-2008, 04:59 PM
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
10-15-2008, 02:15 AM
Thanks for reply, no error but it's not working...

NogDog
10-15-2008, 02:27 AM
Maybe you need to use WHERE IS NULL `accounts`.`acct` ?

jurasekk
10-15-2008, 03:23 PM
Still not working... :(

NogDog
10-15-2008, 03:26 PM
Is the `acct` field actually NULL, or just an empty string?

jurasekk
10-15-2008, 04:28 PM
Delete if `acct` doesn't match

chazzy
10-15-2008, 06:25 PM
what about something simpler


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

jurasekk
10-16-2008, 09:34 AM
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
10-16-2008, 09:20 PM
sorry forgot parenthesis


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

jurasekk
10-17-2008, 01:50 AM
Thank you very much, working. :)