根据与其他两个表的关系删除记录

I have three tables in MySQL, table1, table2 and table3. table2 has foreign key of table1 while table3 has foreign key of table2 but not from table1. Structure is something like this:

我在MySQL，table1，table2和table3中有三个表。 table2具有table1的外键，而table3具有table2的外键但不具有table1的外键。结构是这样的：

table1

表格1

Name   | type
------------------------
d_id   | int
d_name | varchar(100)

table2

表2

Name   | type
---------------------------------------
s_id   | int
d_id   | int (foreign_key table1 d_id)
s_name | varchar(100)

table3

表3

Name    | type
----------------------------------------
m_id    | int
s_id    | int (foreign_key table2 s_id)
m_value | int

I can easily select and join the information on these three tables but to delete base on similar join I just don't know how to do. This is how I did to select data.

我可以轻松选择和加入这三个表的信息，但基于类似的连接删除我只是不知道该怎么做。这就是我选择数据的方式。

SELECT * FROM table3 
JOIN table2 ON table3.s_id = table2.s_id 
JOIN table1 ON table2.d_id = table1.d_id

Now, I want to delete some values on table3 where table3 = table2 = table1 how do I do that?

现在，我想删除table3上的一些值，其中table3 = table2 = table1我该怎么做？

4 个解决方案

#1

Considering you want to delete data from Table3 which is not reference by any foreign key, I'm using dummy data to demonstrate this query. Note, you should only have problems when deleting data from Table1 and Table2 which is reference by foreign keys, You can't delete data from master table without deleting its reference from child tables as Primary and Foreign key forces a Referential Integrity constraint.

考虑到您要从Table3中删除任何外键未引用的数据，我使用虚拟数据来演示此查询。注意，在从外键引用的Table1和Table2中删除数据时，您应该只有问题。您不能从主表中删除数据而不从子表中删除其引用，因为Primary和Foreign键强制引用完整性约束。

create table table1
(
d_id    int PRIMARY KEY
,d_name  varchar(100)
);


create table table2
(
s_id    int PRIMARY KEY
,d_id    int 
,s_name  varchar(100)
,FOREIGN KEY (d_id) REFERENCES table1(d_id)
);

create table table3
(
m_id     int PRIMARY KEY
,s_id     int 
,m_value  int
,FOREIGN KEY (s_id) REFERENCES table2(s_id)
);

insert into table1 values (1,'D1');
insert into table1 values (2,'D1');
insert into table1 values (3,'D1');

insert into table2 values (10,1,'S1');
insert into table2 values (11,2,'S2');
insert into table2 values (12,3,'S3');

insert into table3 values (100,10,23);
insert into table3 values (101,11,34);
insert into table3 values (102,11,45);
insert into table3 values (103,12,54);

select * from table3;

DELETE T3
FROM  Table3 as T3 INNER JOIN Table2 as T2 ON T3.s_id = T2.s_id
                   INNER JOIN Table1 as T1 ON T1.d_id = T2.d_id;

--All the Records from Table3 will be deleted now, add where clause in above
--Query to limit your delete.

select * from table3;

#2

If you've happily written a select statement that selects the list of IDs that should be deleted, you can then delete those rows:

如果您乐意编写一个select语句来选择应删除的ID列表，则可以删除这些行：

DELETE FROM sometable WHERE ID IN 
(
  SELECT idcolumn FROM othertables JOIN otherothertables WHERE blahblah ...
)

#3

DELETE FROM table3 t3
INNER JOIN table2 t2 ON t3.s_id = t2.s_id
INNER JOIN table1 t1 ON t2.d_id = t1.d_id
WHERE t3.s_id=t2.s_id AND t2.d_id=t1.d_id

#4

Assuming that you want to delete all data from table3 which corresponds to d_name=fdiengdoh in table1, you can use below query.

假设您要删除table3中与table1中的d_name = fdiengdoh相对应的所有数据，您可以使用以下查询。

DELETE FROM table3 as t3
inner join table2 as t2 on t3.s_id = t2.s_id
inner join table1 as t1 on t2.d_id = t1.d_id
AND t1.d_name = 'fdiengdoh'

If you need to remove all corresponding data, just remove the last line from above query.

如果您需要删除所有相应的数据，只需从上面的查询中删除最后一行。

Hope this will help.

希望这会有所帮助。