Oracle 查询帮助

发布于 2024-08-30 01:23:10 字数 397 浏览 3 评论 0原文

我想删除表 A 中字段名称 class="10010" 和表 B 中字段名称 AentryId = BentryId 的所有记录。

如果我删除与 className= 匹配的entryId 12 10010 从表 A 中删除，同时相同的 id 也应该从表 B 中删除。

表 A：

AentryId  className     
12      10010
13      10011
14      10010
15      10011

表 B：

BentryId  name   
12         xyz
13         abc
14         aaa

原文

I want to delete all the records where field name class="10010" from Table A and AentryId = BentryId from Table B.

if i delete the entryId 12 which matches className=10010 from Table A and the same time that same id should delete from Table B also.

Table A:

AentryId  className     
12      10010
13      10011
14      10010
15      10011

Table B:

BentryId  name   
12         xyz
13         abc
14         aaa

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北座城市 2024-09-06 01:23:10

最简单的方法是通过使用 CASCADE DELETE 定义的外键：

alter table B 
  add constraint b_a_fk foreign key (BentryId)
  references A (AentryId)
  on delete cascade
/

如果从 A 中删除一行，则 B 中的所有相关记录都将被自动删除。

当然，强制使用外键意味着您无法使用不引用 A 中预先存在的 AentryId 的 BentryId 在 B 中创建任何行。这通常是一件可取的事情，但并非每个数据模型都强制关系完整性。

编辑

删除约束真的再简单不过了......

alter table B 
  drop constraint b_a_fk 
/

The easiest way to do this is through a foreign key defined with CASCADE DELETE:

alter table B 
  add constraint b_a_fk foreign key (BentryId)
  references A (AentryId)
  on delete cascade
/

If you delete a row from A all its dependent records in B are automatically deleted.

Of course, enforcing a foreign key means that you cannot create any rows in B with a BentryId which does not reference a pre-existing AentryId in A. This is normally a desirable thing but not every data model enforces relational integrity.

edit

Dropping the constraint really couldn't be simpler...

alter table B 
  drop constraint b_a_fk 
/

回复收藏 0 原文

り繁华旳梦境 2024-09-06 01:23:10

以下可能是您正在寻找的最终查询

delete form TableA where   class="10010" and AentryId in ( select BentryId from tableB)

级联删除是允许您从子表和表单主表中删除数据的选项之一

，否则您可以编写如下查询

   declare @T table (id int)
   insert into @T  
      select AentryId form TableA where   class="10010" and AentryId in ( select BentryId from tableB)

  delete form TableA where  AentryId in ( select id from @T)
  delete form TableB where  BentryId in ( select id from @T)

Following may be final query you are looking for

delete form TableA where   class="10010" and AentryId in ( select BentryId from tableB)

Cascade delete is one of the option that allow you to delete data from child and form primary table

otherwise you can write query like below

   declare @T table (id int)
   insert into @T  
      select AentryId form TableA where   class="10010" and AentryId in ( select BentryId from tableB)

  delete form TableA where  AentryId in ( select id from @T)
  delete form TableB where  BentryId in ( select id from @T)

回复收藏 0 原文

~没有更多了~