首页 文章

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

提问于
浏览 次
0

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

表格1

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

表2

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

表3

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

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

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

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

mysql sql join

4 回答

  • 0

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

    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;
    回复于
  • 1

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

    DELETE FROM sometable WHERE ID IN 
(
  SELECT idcolumn FROM othertables JOIN otherothertables WHERE blahblah ...
)
    回复于
  • 0
    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
    回复于
  • 0

    假设您要删除 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'

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

    希望这会有所帮助 .

    回复于

相关问题