simulare CASCADE su myisam

Ciao Gianni,

ho bisogno di eliminare tutti i record con un dato ID da 5/6 tabelle in un sol colpo, ma visto che uso myisam non posso utilizzare CASCADE per eliminare tutti i record annessi al record eliminato.

Mi suggeriresti un modo semplice e veloce che richieda al più due query?

le tabelle sono:

announces -> contenente il campo da eliminare

tutte le altre contengono riferimenti, remoti, all'id eliminato

images

thumbnails

prices

advanced

descriptions

faves

orders

 :bye:

inviato 9 anni fa
Andrea Turso
Andrea Turso
86
X 0 X

tratto da http://dev.mysql.com/doc/refman/5.0/en/delete.html

You can specify multiple tables in a DELETE  statement to delete rows from one or more tables depending on the particular condition in the WHERE clause. However, you cannot use ORDER BY or LIMIT in a multiple-table DELETE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 13.2.7.1, JOIN Syntax.

For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table 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 have additional tables that are used only 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 tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

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

The syntax allows .* after the table names for compatibility with Access.

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Note: If you provide an alias for a table, you must use the alias when referring to the table:

DELETE t1 FROM test AS t1, test2 WHERE ...

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 ...

Currently, you cannot delete from a table and select from the same table in a subquery.

 :bye:

risposto 9 anni fa
Gianni Tomasicchio
X 0 X

si avevo letto ma non riesco a farla andare :(

:bye:

risposto 9 anni fa
Andrea Turso
Andrea Turso
86
X 0 X

DELETE announces, images, thumbnails, prices, advanced, descriptions, faves, orders

FROM announces, images, thumbnails, prices, advanced, descriptions, faves, orders

WHERE

announces.id = 123 AND

images.announces_id = announces.id AND

thumbnails.announces_id  = announces.id AND

...

 :bye:

risposto 9 anni fa
Gianni Tomasicchio
X 0 X

grazie :2funny:

ne avevo provata una molto simile ma non andava  :dunno:

risposto 9 anni fa
Andrea Turso
Andrea Turso
86
X 0 X
DELETE
sm_announces,
sm_images,
sm_thumbnails,
sm_prices,
sm_advanced,
sm_descriptions,
sm_faves,
sm_orders
FROM
sm_announces,
sm_images,
sm_thumbnails,
sm_prices,
sm_advanced,
sm_descriptions,
sm_faves,
sm_orders
WHERE
sm_announces.anid=1
AND
sm_images.anid=sm_announces.anid
AND
sm_thumbnails.anid=sm_announces.anid

Ma : Query returned no results , questo è normale :2funny: è un delete

peccato che : le righe sono ancora lì!!! ???

:bye:

risposto 9 anni fa
Andrea Turso
Andrea Turso
86
X 0 X

fai prima una SELECT con lo stesso criterio della DELETE per vedere se effettivamente hai individuato delle righe da cancellare.

 :bye:

risposto 9 anni fa
Gianni Tomasicchio
X 0 X
SELECT
sm_announces.*,
sm_images.*,
sm_thumbnails.*,
sm_prices.*,
sm_advanced.*,
sm_descriptions.*,
sm_faves.*,
sm_orders.*
FROM
sm_announces,
sm_images,
sm_thumbnails,
sm_prices,
sm_advanced,
sm_descriptions,
sm_faves,
sm_orders
WHERE
sm_announces.anid=1

mi ritorna solo le intestazioni con il nome del campo senza record.

ho dovuto aggiungere .* altrimenti mi diceva che non trova il campo.

risposto 9 anni fa
Andrea Turso
Andrea Turso
86
X 0 X

Resolved

DELETE
sm_announces.*,sm_orders.*,sm_prices.*,sm_thumbnails.*,sm_descriptions.*, sm_images.*
FROM
sm_announces,sm_orders,sm_prices,sm_thumbnails,sm_descriptions,sm_images
WHERE
sm_announces.anid=1 AND
sm_orders.anid=sm_announces.anid AND
sm_prices.anid=sm_announces.anid AND
sm_thumbnails.anid=sm_announces.anid AND
sm_images.imid=sm_thumbnails.imid

:D

:bye:

risposto 9 anni fa
Andrea Turso
Andrea Turso
86
X 0 X
Effettua l'accesso o registrati per rispondere a questa domanda