[SOLVED] Delete from multiple tables in one query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

[SOLVED] Delete from multiple tables in one query

Post 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
Last edited by Skittlewidth on Fri Jul 16, 2004 5:07 am, edited 1 time in total.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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]
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post 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!
Last edited by Skittlewidth on Thu Jul 15, 2004 6:36 am, edited 1 time in total.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
Post Reply