我如何...在单个SQL查询中删除两个表中的行 [英] How do i...delete the rows from two table in single SQL query

问题描述

你好朋友，



i有两张桌子名字



1.

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.

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'有三列名为'Id' ，'Bill_No'，'日期'



2.'BILL_ITEMS'在此表中还有三列'Id'，'Bill_Id'，'Product_Id'列'Bill_Id '与表'BILL'中的列'Bill_No'相同。



现在我想删除账单记录，我不能使用关系键（CASCADE方法）

所以我想删除对Singl的记录e查询或使用SUBQUERY方法。我的代码如下所示，但我遇到了错误。



我尝试过：

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

推荐答案

以下内容：

Something along the lines of:

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)

您无法在一个查询中执行此操作，因为您可以'设置带有级联删除的外键 [ ^ ]。所以在两个查询中连续执行。试试这个：

首先，根据bill表中的日期范围从bill_items表中删除：

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'
}

，然后是

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.

尝试使用存储过程。

give it a try with a stored procedure.

create storedprocedure yourprocedurename
as
begin

delete from firsttable where somecondition

delete from secondtable where somecondition

end

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