----- Original Message ----- From: "Merlin" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Sunday, October 09, 2005 1:31 PM Subject: deleting rows in 2 tables
> Hi there, > > I am wondering if there is a possiblity to delete rows in more than one > table with one query. > > At the moment I am doing this: > > I do have at the moment 3 querys!: > # get table1_id > SELECT table1_id > from ... > WHERE ID = ... > > # delete dependent entry > DELETE > FROM $DB.$T5 > WHERE > ID = '$data[table1_id]' > LIMIT 1 > > # delete main entry > DELETE > FROM $DB.$T4 > WHERE > ID = '$data[id]' > LIMIT 1 > > Is there a better solution as this? > Have you considered using foreign keys with DELETE CASCADE rules? Basically, if you define the dependent tables as children of the first (parent) table via the appropriate foreign keys and primary keys, and if you establish DELETE CASCADE on the foreign keys, you could be sure that whenever you deleted a row in the parent table, all dependent rows in all dependent tables are deleted automatically. In other words, you set up the rules in your table definitions and then simply delete what you like from the parent table; the dependent rows will be deleted for you without you having to write any explicit DELETE statements for the dependent tables. For example, if your parent table was EMPLOYEE and your dependent tables were EMPLOYEE_CARS and EMPLOYEE_CLAIMS, you'd have the definitions set up roughly like this: create table EMPLOYEE empno integer not null, ... primary key(empno)); create table EMPLOYEE_CARS empno integer not null, licence_plate_state char(2) not null, licence_plate_number char(8) not null, ... primary key(empno, licence_plate_state, licence_plate_number), foreign key(empno) references EMPLOYEE on delete cascade)); create table EMPLOYEE_CLAIMS empno integer not null, claim_number integer not null,, ... primary key(empno, claim_number), foreign key(empno) references EMPLOYEE on delete cascade)); Then, once the tables are defined, all your program needs to do is delete a given employee and the employee's cars and claims will be deleted automatically, via the DELETE CASCADE rules in the definitions of the dependent tables: delete from EMPLOYEE where empno = 10; --- If you want to use this approach, I believe you have to be using InnoDB tables; I don't think the other "engines" support foreign keys. By the way, this whole concept is called "Referential Integrity" and is very useful for ensuring that your tables are consistent with one another. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.13/126 - Release Date: 09/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
