Single SQL Query to delete records in two tables

Question

1.80/5 (5 votes)

See more:

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........

Posted 20-Aug-12 23:53pm

Karthik J, Coimbatore

Updated 17-Sep-17 23:54pm

Add a Solution

Comments

Archana K 21-Aug-12 6:33am

Specify foreign key for the details tables which references to the primary key of master and set Delete rule = Cascade .

Now when u delete a record from the master table all other details table record based on the deleting rows primary key value, will be deleted automatically.

So in that case a single delete query of master table can delete master tables data as well as child tables data.

5 solutions

Solution 4

Try this:

SQL

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

Also have look on similar answer: How to delete multiple rows from different tables in single query[^]

Posted 21-Aug-12 0:17am

Prasad_Kulkarni

Updated 21-Aug-12 0:19am

v2

Comments

[no name] 21-Aug-12 6:24am

But it is not possible in sql 2008!!!

Prasad_Kulkarni 21-Aug-12 6:46am

Why so?

Maciej Los 21-Aug-12 17:12pm

Good answer, my 5!

Prasad_Kulkarni 22-Aug-12 0:01am

Thank you Iosmac! Glad to see you.

vikasvanvi 7-Feb-14 0:18am

not working in sql 2008

Karmesh_Madhavi 28-Feb-15 8:05am

if there are 3 table.?, so how i can delete each relation records.?

NIRMAL AJAY 8-Feb-17 9:58am

i think this is not possible in single query, because sql does not provide multiple delete statement, but only method is to set the cascade option in relationship, then you will be able to delete more than 2 table as you want

Solution 5

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[^]

Posted 21-Aug-12 0:17am

ssd_coolguy

Solution 6

hi delete two table from asp .net code

create table state
sid,
sname

create table city

Posted 28-Jul-14 21:56pm

Member 10814381

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sunil Kumar Pandab · Accepted Answer · 2012-08-21T00:11:00

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

Niju1 · Accepted Answer · 2012-08-21T00:10:00

Solution 1

Hi

You can have foriegn key relationship for subcategory with delete cascade option
if you have foriegn key you can alter it or can add it

SQL

ALTER TABLE subcategory
ADD CONSTRAINT fk_category
FOREIGN KEY (cat_id)
REFERENCES  Category(cat_id)
ON DELETE CASCADE;

Posted 21-Aug-12 0:10am

Niju1

Updated 21-Aug-12 11:00am

v2