I have a badly designed database with 5 tables combining to form a single record. (Each record contains in excess of 60 fields.) 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.
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.
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.
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!
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 ...
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.
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.
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.
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:
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.
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'