DELETE, aber keine Joins?

[Seikilos]

Hab dieses Query

DELETE
               FROM `hs_dir_links` l
               INNER JOIN hs_dir_nest n ON n.lft
               BETWEEN n2.lft
               AND n2.rgt
               INNER JOIN hs_dir_nest n2 ON l.cat_id = n.nest
               WHERE n2.nest = ".$did

Aber es kommt
MySQL error description : 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 'l
INNER JOIN hs_dir_nest n ON n.lft

[asp2php]

so 'n JOIN nicht, aber du kannst:

delete t1.*, .... from t1, t2, t3 where t1.a=t2.b and t2.c=t3.d ....

[Seikilos]

Der meckert aber schon beim alias für hs_dir_links und ich muss, wie man sieht die hs_dir_nest zweimal benutzen, daher sind aliases wichtig, aber er meckert ja, wie gesagt schon beim l

[xManUx]

Niemand hat gesagt, dass man im WHERE-Teil mit Alias arbeiten kann. Aber wenn du t1.namedoppelt = t2.namedoppelt benutzt, brauchst du auch keine Alias...

[chansel0049]

ist auch kein Wunder

FROM `hs_dir_links` l INNER JOIN hs_dir_nest n ON n.lft
BETWEEN n2.lft

an der Stelle ist n2 noch garnicht im Join enthalten

ganz zu schweigen vom Rest der Join-Anweisung

FROM `hs_dir_links` l
               INNER JOIN hs_dir_nest n ON n.lft
               BETWEEN n2.lft
               AND n2.rgt
               INNER JOIN hs_dir_nest n2 ON l.cat_id = n.nest 

Du bindest n2 über Felder die gar nicht in n2 sind ...

[Seikilos]

Der query oben mit "SELECT Spalte" anstatt DELETE und genau diesem Syntax funktioniert problemlos, auch wenn der Join erst später kommt

[chansel0049]

Funktionieren mag sein, aber richtige ergebnisse liefern würde ich bezweifeln ... (Abgesehen davon dass die Query nicht ordentlich lesbar wird)

Dennoch fehlt bei mult-table deletes die Anweisung von welcher tabelle gelöscht werden soll ...

From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE.

The first multiple-table DELETE syntax is supported starting from MySQL 4.0.0. The second is supported starting from MySQL 4.0.2. The table_references part lists the tables involved in the join. Its syntax is described in Section 13.2.7.1, “JOIN Syntax”.

For the first syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and also have additional tables that are used for searching:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three files when searching for rows to delete, but delete matching rows only from tables t1 and t2.

The examples show inner joins using the comma operator, but multiple-table DELETE statements can use any type of join allowed in SELECT statements, such as LEFT JOIN.

...
Note: In MySQL 4.0, you should refer to the table names to be deleted with the true table name. In MySQL 4.1, you must use the alias (if one was given) when referring to a table name:
.....


Cross-database deletes are supported for multiple-table deletes, but in this case, you must refer to the tables without using aliases. For example:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

Hab mal einen Blick : http://dev.mysql.com/doc/mysql/en/delete.html