How do i...delete the rows from two table in single SQL query

Question

0.00/5 (No votes)

See more:

hello friend,

i have two tables name

1.

CREATE TABLE [Bill]
(       [Id] [int] IDENTITY(1,1) NOT NULL,         --Primary key
	    [Bill_No] [int] NOT NULL,
	    [Date] [datetime] NOT NULL
)

2.

CREATE TABLE [BILL_ITEMS]
(
	           [Id] [int] IDENTITY(1,1) NOT NULL,--Primary key
	           [Bill_Id] [int] NOT NULL,          --Foreign key
	           [Product_Id] [int] NOT NULL
)

1. 'BILL' has three column named 'Id', 'Bill_No', 'Date'

2. 'BILL_ITEMS' also has three column 'Id', 'Bill_Id', 'Product_Id' in this table Column 'Bill_Id' is same as Column 'Bill_No' that is in table 'BILL'.

now i want to delete the bills records, i can't use Relationship keys (CASCADE Method)
so i want to delete records against Single query or using SUBQUERY method. my code is given below but i faced error.

What I have tried:

DELETE FROM Bill WHERE Bill_No EXISTS IN=
                                       
               (     SELECT  B.Id, B.Bill_No , B.Date, I.Id, I.Bill_Id, I.Product_Id
                     FROM
                     Bill AS B, BILL_ITEMS AS I WHERE B.Bill_No = I.Bill_Id
                     AND
                     B.Date >= CAST('2017-02-08 12:07:40.330' AS Date)
                     AND
                     B.Date <= CAST('2017-02-19 12:09:13.560' AS Date)
               )

Posted 8-Feb-17 3:22am

NIRMAL AJAY

Updated 8-Feb-17 18:46pm

v2

Add a Solution

5 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)

Michael_Davies · Answer 1 · 2017-02-08T03:36:00

Solution 2

Something along the lines of:

SQL

DELETE FROM [Bill],[BILL_ITEMS] USING [Bill] 
LEFT JOIN [BILL_ITEMS] ON [BILL_ITEMS].Bill_No=[Bill].Bill_Id
WHERE [Bill].Date >= CAST('2017-02-08 12:07:40.330' AS Date)
AND
[Bill].Date <= CAST('2017-02-19 12:09:13.560' AS Date)

Posted 8-Feb-17 3:36am

Michael_Davies

Updated 8-Feb-17 3:37am

v2

Comments

NIRMAL AJAY 8-Feb-17 10:25am

i faced errors, i think sql does not allows COMMA's in DELETE statement (DELETE FROM[Bill],[BILL_ITEMS]),i don't know how can you used this statement

Michael_Davies 8-Feb-17 11:25am

Works in MySQL no problem.

CHill60 8-Feb-17 12:18pm

I'm guessing that you are using SQL Server then - if you had tagged your question correctly we would have known that

CHill60 8-Feb-17 12:28pm

5'd. It doesn't work in SQL Server but as the OP did not specify which version of sql they were using that's not your fault.

Peter Leow · Answer 2 · 2017-02-08T05:00:00

You can't do it in one query since you can't set foreign Keys with cascade delete[^]. So do it in two queries successively. Try this:
First, delete from the bill_items table based on the date range in bill table:

DELETE FROM bill_items bi WHERE EXISTS
(
SELECT * FROM bill b WHERE b.bill_no = bi.bill_id AND [Date] BETWEEN '2017-02-08' AND '2017-02-19'
}

, followed by

DELETE FROM bill WHERE [Date] BETWEEN '2017-02-08' AND '2017-02-19'

You can use semi-colon to join these two queries and submit them to the server in one go.

sachin.vishwa90 · Answer 3 · 2017-02-08T05:06:00

Solution 5

hi, give it a try with a stored procedure.

SQL

create storedprocedure yourprocedurename
as
begin

delete from firsttable where somecondition

delete from secondtable where somecondition

end

Posted 8-Feb-17 5:06am

sachin.vishwa90

Comments

CHill60 8-Feb-17 12:27pm

Only 3 as you should have pointed out to delete from bill_items first (because of the foreign key constraint)

Richard Deeming · Answer 4 · 2017-02-08T05:06:00

If you can't use a foreign key with cascading delete (why not?), then you can't do this with a single statement. Without cascading deletes, a DELETE statement can only affect a single table.

Assuming Microsoft SQL Server, something like this should work:

SQL

BEGIN TRY;
BEGIN TRANSACTION;
    
    DELETE
    FROM 
        I
    FROM 
        Bill_Items As I
        INNER JOIN Bill As B
        ON B.Bill_Id = I.Bill_No
    WHERE
        B.Date Between @MinDate And @MaxDate
    ;
    
    DELETE 
    FROM 
        Bill 
    WHERE 
        Date Between @MinDate And @MaxDate
    ;
    
    COMMIT;
END TRY
BEGIN CATCH;
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

NIRMAL AJAY · Answer 5 · 2017-02-08T18:46:00

Solution 7

this works properly

DELETE FROM Bill WHERE EXISTS (SELECT ... )

Posted 8-Feb-17 18:46pm