deleteing 3 table's data with single query

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

1st table name= tblA

fields:
ID int
NAME varchar

2nd table name = tblB

fields:
F int
eName varchar
ID int

3rd table name = tblC

fields:
G int
Gname varchar
ID int
F int

i have to delete ID from 3 tables where in three tables ID is same. if ID='1' in three tables the all record inside 3 tables should delete ..
please tell me...

Posted 12-Dec-12 19:38pm

sreeCoderMan

Add a Solution

Comments

[no name] 13-Dec-12 3:28am

Can you please specify whether your 3 tables are inter related or not?
I mean to say is there any relation between your tables?

sreeCoderMan 13-Dec-12 3:31am

yes you can see ID in three tables

3 solutions

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)

__TR__ · Answer 1 · 2012-12-12T21:10:00

You can include your delete statements in a transaction so that if anything goes wrong with any of the grouped statements, all changes will be aborted.

Here is how you will do it.

SQL

BEGIN TRY
  BEGIN TRANSACTION

    DECLARE @Id INT
    SET @Id = 1

    DELETE FROM tblC WHERE ID = @Id
    DELETE FROM tblB WHERE ID = @Id
    DELETE FROM tblA WHERE ID = @Id


  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
  --Exception handling
 END CATCH

Patel Shailendra · Answer 2 · 2012-12-12T22:11:00

1st table name= tblA

fields:
ID int
NAME varchar

2nd table name = tblB

fields:
F int
eName varchar
ID int

3rd table name = tblC

fields:
G int
Gname varchar
ID int
F int

Keep in mind always start deleting from bottom to top in this condition you have to delete first tblC record then tblB and then tblA

eg

delete from tblC where ID=Value
delete from tblB where ID=Value
delete from tblA where Id=Value

value must be common in all the field.
or
you can create procedure and just pass id of the record which you want to delete.

eg

Create procedure DemoDelete(@value int)
as
begin
delete from tblC where ID=@value
delete from tblB where ID=@value
delete from tblA where Id=@value
end

Code-Hunt · Answer 3 · 2012-12-12T20:06:00

Solution 2

SQL

You cannot DELETE from multiple tables with a single expression in SQL 2005 - or any other standard SQL for that matter. 

The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger.

Posted 12-Dec-12 20:06pm

Code-Hunt

Comments

sreeCoderMan 13-Dec-12 2:09am

can you please write the code for me.....

[no name] 13-Dec-12 2:12am

Even i m also trying the same thing..i got this much information till now so shared with u.as soon as i get solution will share.
and don't forget to vote for my answer..

sreeCoderMan 13-Dec-12 4:22am

sure

deleteing 3 table's data with single query

3 solutions

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0