单个SQL查询删除两个表中的记录 [英] Single SQL Query to delete records in two tables

问题描述

我需要单个SQL查询来删除两个表中的记录而不使用触发器选项而没有两个删除命令。



表格如下：



表1：类别

cat_id

cat_name



表2 ：subategory

subcat_id

cat_id

cat_name



在此先感谢.. ......

I need Single SQL Query to delete records in two tables without using trigger option and without two delete commands.

Table look like:

Table 1: Category
cat_id
cat_name

Table 2: subcategory
subcat_id
cat_id
cat_name

Thanks in advance........

推荐答案

试试这个：

Try this:

DELETE c.*, sb.*
FROM Category c
LEFT JOIN subCategory sb ON c.cat_id = sb.cat_id
WHERE c.cat_id = 1

也可以看一下类似的答案：如何在单个查询中删除不同表中的多行 [ ^ ]

你可以去删除级联： -



SQL> create table test16（id number primary key，name varchar2（10））;



创建表。





SQL>创建表test17（id号，地址varchar2（10），外键（id）引用test16（id）on

delete cascade）;



创建表。





--------------------- -------------------------------------------------- -

这些是表格的行



SQL> select * from test16;



ID NAME

--------- ----------

100 srini

200 ram

300 sam



SQL> select * from test17;



ID ADDRESS

--------- ----------

100 add1

200 add2

300 add3



---- -------------------------------------------------- -----------------



删除一个表中的行，以便删除外键儿童桌。



SQL>从test16删除id = 300;



1行删除。



------ -------------------------------------------------- -------------

并检查行是否被删除



SQL> select * from test16;



ID NAME

--------- ----------

100 srini

200 ram



SQL> select * from test17;



ID ADDRESS

--------- ----------

100 add1

200 add2

You can go for on delete cascade :-

SQL> create table test16 (id number primary key,name varchar2(10));

Table created.


SQL> create table test17 (id number,address varchar2(10), foreign key (id) references test16(id) on
delete cascade);

Table created.


------------------------------------------------------------------------
these are the rows of the tables

SQL> select * from test16;

ID NAME
--------- ----------
100 srini
200 ram
300 sam

SQL> select * from test17;

ID ADDRESS
--------- ----------
100 add1
200 add2
300 add3

-----------------------------------------------------------------------

deleting the row in one table , so that it will delete the foreign keys of the child table.

SQL> delete from test16 where id=300;

1 row deleted.

---------------------------------------------------------------------
And checking whether the rows are deleted or not

SQL> select * from test16;

ID NAME
--------- ----------
100 srini
200 ram

SQL> select * from test17;

ID ADDRESS
--------- ----------
100 add1
200 add2

试试以下链接： -



http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query [ ^ ]

http://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a [ ^ ]

try the below links:-

http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query[^]
http://stackoverflow.com/questions/1714545/delete-rows-from-multiple-tables-using-a-single-query-sql-express-2005-with-a[^]

这篇关于单个SQL查询删除两个表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！