Page 1 of 1

[SOLVED] Delete from multiple tables in one query

Posted: Tue Jul 13, 2004 4:22 am
by Skittlewidth
I have a badly designed database with 5 tables combining to form a single record. (Each record contains in excess of 60 fields.) 8O I need to be able to simultaneously delete from four of these tables to remove a record. All of these contain the auto_increment column 'mat_id' to enable me to link the scattered parts of a record together.

I thought the following would work:

Code: Select all

delete from material, hazards, procedures, task_specific where mat_id = $delete_me
failing that I tried

Code: Select all

delete from material, hazards, procedures, task_specific where material.mat_id = $delete_me and hazards.mat_id = material.mat_id 
	  and hazards.mat_id = procedures.mat_id and procedures.mat_id = task_specific.mat_id
but it keeps on returning a syntax error. Can anyone tell me where I'm going wrong (apart from having a badly designed database in the first place!)? Thanks

Posted: Tue Jul 13, 2004 11:10 am
by kettle_drum
Its sometimes better to have multiple queries instead of one long one as mysql (i guess thats what your using) isnt so fast at dealing with long queries.

Posted: Tue Jul 13, 2004 11:29 am
by feyd
mysql handles long queries just fine..
DELETE Syntax wrote:Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       tbl_name[.*] [, tbl_name[.*] ...]
       FROM table_references
       [WHERE where_definition]


Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM tbl_name[.*] [, tbl_name[.*] ...]
       USING table_references
       [WHERE where_definition]

Posted: Tue Jul 13, 2004 12:59 pm
by pickle
I co-worker of mine got something to work using syntax like:

Code: Select all

delete table1, table2, table3 from table1, table2, table3 where ......
Don't ask me how or why - 'cuz I don't know.

Posted: Thu Jul 15, 2004 6:09 am
by Skittlewidth
feyd, I did refer to the mysql manual to make up my long query and it didn't seem to work. The server is using mysql 4.something so I can't see why not.

I'll probably end up using multiple queries, but I'll take another quick look.
Thanks for all your suggestions folks! :D

Posted: Thu Jul 15, 2004 6:19 am
by JAM
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:

In MySQL 4.0:
DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1:
DELETE t1 FROM test AS t1, test2 WHERE ...
Perhaps this is the issue?

You also tried:

Code: Select all

delete from material,...
...and that is not correct syntax-wise. What exactly are you trying to delete? Compare with my example/quote.

Posted: Thu Jul 15, 2004 6:35 am
by Skittlewidth
Ah! Got it, thanks. It seemed a bit long winded typing out

Code: Select all

delete materials, hazards, procedures from materials, hazards, procedures...
but if its got to be done theres no arguing with it!
Can you quickly explain how or why you would use an alias though? Not that it matters, I'm using 4.0 it turns out.

Thanks!

Posted: Thu Jul 15, 2004 6:36 am
by CoderGoblin
Does MYSQL have constraints/foreign keys similar to POSTGRES ?

If things are linked (FOREIGN KEYS) in POSTGRES you can set it to ON DELETE CASCADE. This deletes all items in other tables referencing the row in question automatically.

Posted: Fri Jul 16, 2004 9:00 am
by JAM
CoderGoblin wrote:Does MYSQL have constraints/foreign keys similar to POSTGRES ?
Similiar, yes.
Starting from MySQL 3.23.44, InnoDB features foreign key constraints.
...and, what you mention is indeed true...
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, delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.
Skittlewidth wrote:Can you quickly explain how or why you would use an alias though?
Well, how is allready visible in my post above. Why? Not entitled to say. Someone with abit more technical brain might answer this better.

Posted: Mon Nov 01, 2004 2:43 pm
by Weirdan
JAM wrote: Well, how is allready visible in my post above. Why? Not entitled to say. Someone with abit more technical brain might answer this better.
The reason of why table names should be used more twice (delete tablename from tablename....) is that one might want to use several tables to form WHERE clause but delete the rows from one specific table. For example, we might had two tables linked in child-parent relation (i.e. items-categories). We decided to remove one category, but somehow forgot to remove those items in it. No problem, simple query comes in handy:

Code: Select all

delete items from items left join categories on items.cat_id = categories.id where categories.id is null
In other words, DELETE operates on undelying database structure but its WHERE clause operates on cartesian product of the tables specified in the FROM clause.

Posted: Wed Nov 03, 2004 8:41 am
by kendall
Ok guys i used this query to delete related information ONLY from multipule tables

Code: Select all

DELETE FROM products, price_options, product_options USING products, price_options, product_options WHERE product_options.ProductID = products.ProductID AND price_options.ProductID = products.ProductID AND products.ProductID = 'test'
Kendall