Syntax error on multiple-table deletion

Question

When I run this:

DELETE FROM folders AS fo
LEFT OUTER JOIN files AS fi 
ON fo.folderId = fi.folderId
WHERE fi.folderId IS NULL AND (fo.folderId IN (63,1000))

... I get a syntax error:

#1064 - 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 'AS fo LEFT OUTER JOIN files AS fi ON fo.folderId = fi.folderId WHERE fi.fold' at line 1

The code is based in a SELECT query that returns the intended rows. What's the reason for the error message?

#1064 - 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 'AS fo LEFT OUTER JOIN files AS fi ON fo.folderId = fi.folderId WHERE fi.fold' at line 1 — jon
– jon, Commented Mar 15, 2013 at 11:26

Álvaro González · Accepted Answer · 2013-03-15 11:31:02Z

1

It isn't a surprise that it "doesn't work". When I run your code I get a syntax error:

SQL Error (1064): 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 'AS fo LEFT OUTER JOIN files AS fi ON fo.folderId = fi.folderId WHERE fi.fold' at line 1

The correct syntax is:

DELETE fo -- Note I've added table name
FROM folders AS fo
LEFT OUTER JOIN files AS fi 
ON fo.folderId = fi.folderId
WHERE fi.folderId IS NULL AND (fo.folderId IN (63,1000));

answered Mar 15, 2013 at 11:31

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Álvaro González Over a year ago

@jon I see you posted the relevant info later. I've edited the question to make it look as it should have been from the beginning.

Rachcha · Accepted Answer · 2013-03-15 11:28:11Z

1

DELETE
  FROM folders fo
 WHERE NOT EXISTS (SELECT 1
                     FROM files fi
                    WHERE fi.folderId = fo.folderId)
   AND fo.folderId IN (63, 1000)

edited Mar 15, 2013 at 11:28

answered Mar 15, 2013 at 11:22

Rachcha

8,8969 gold badges52 silver badges70 bronze badges

3 Comments

jon Over a year ago

Thanks Rachcha but the JOIN is needed so that I only delete folders that are not in the files table.

Rachcha Over a year ago

Just a min then, I'll edit my answer and give you an appropriate query for the job.

jon Over a year ago

Thanks for your help Rachcha but alvaro has got thee answer..I plus oned your answer as it works, but wasn't actuauly the answer to my question... thanks again j

Collectives™ on Stack Overflow

Syntax error on multiple-table deletion

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related