mySQL: Deleting with JOIN ?

Question

Hi everybody,

This problem is giving me a real headache.

I have two tables in my database that solves sorting of categories with the help of a design called "Closure table". The first table is called categories, and the other one is named categoriesPaths.

Categories is simply a language-based table:

id | name
----------------
0  | Category 1
1  | Category 2

CategoriesPaths looks like this:

parent | children | level
-----------------------
1     | 1         | 0
1     | 2         | 1
2     | 2         | 0

To delete a path together with it's eventual child nodes, I run the following query: ($top is the top parent node level the deletion will begin at)

DELETE d FROM CategoriesPaths children
JOIN CategoriesPaths a USING (children)
WHERE a.parent = $top;

What I want to do is change the query to also delete the representative paths in the categories table. Unfortunately I have not enough SQL knowledge to do this. I tried to to make a workaround with a foreach loop before the query, but that fails to delete, since the categoriestable is referenced to categoriesPaths... By that said, I am pretty much stuck.

If anyone wish to learn more about the closure table design, here's a reference to the slideshare in which I learned it from: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

Thanks a lot!

Answer 1

Well, I would take a different path.

1. Create a temporary table with the id's of all the items to delete
2. Delete CategoriesPaths from temporary table
3. Delete Categories from temporary table
4. Drop the temporary table.

Like this:

-- not tested
CREATE TABLE tmp_to_delete
AS SELECT children.children
FROM CategoriesPaths children
JOIN CategoriesPaths a USING (children)
WHERE a.parent = $top;

DELETE FROM CategoriesPaths WHERE children IN (SELECT children FROM tmp_to_delete)
DELETE FROM Categories WHERE id IN (SELECT children FROM tmp_to_delete)

DROP TABLE tmp_to_delete

Answer 2

Why not use a transaction? http://www.firstsql.com/tutor5.htm