从Sql数据库中删除查询 [英] Delete Query From Sql Database

问题描述

我的数据库中有两个名为invoice和invoiceDetail的表，我必须从invoice表中删除特定的行，所以一旦从父表中删除这些记录，就应该从相关的子表中删除它们表格像invoiceDetail



最好的方法是什么？将InvoiceDetail映射到发票以执行删除的专业方法是什么。 />


谢谢

I have two Tables in my Database named "invoice" and "invoiceDetail", i have to delete specific rows from "invoice" table, So once I delete those records from the parent table then it should be deleted from the related child tables Like "invoiceDetail""

what is best way to do that ? what is professional way to map "InvoiceDetail" to "invoice" to perform the delete.

Thank you

推荐答案

似乎你没有绑定关系中的两个表。现在你删除了一些父记录，并希望清理子表中的孤儿记录数据库。



以下是您需要的查询。（根据您的需要更改）需要）

It seems that you have not bind the both of the tables in relationship. Now you have deleted some of parent records and want to clean your database of orphan records in your child table.

Here is the query of your need. (Change it as per your needs)

DELETE FROM InvoiceDetails
where Id IN(
SELECT a.Id FROM dbo.InvoiceDetails a
LEFT JOIN dbo.Invoice b ON a.Invoice_ID = b.ID WHERE b.ID is NULL)

注意：

这里我假设子表中的forign键名为 Invoice_ID 。

这个伪代码给出了另一种更安全的方法，它考虑了在两个表之间基于主键 + 外键建立关系的约束：



1）打开一个交易（这几乎是所有数据库引擎实现的）;

2）删除来自'子'表（ InvoiceDetails ）的行，选择器应该是外键t的值hat引用'parent'表（ Invoice ）。

This pseudo-code gives an idea of another safer approach that takes into account the constraint of having a 'relation' based on primary-key+foreign-key between both tables:

1) Open one transaction (this is implemented by almost all DB engines);
2) Delete the rows from the 'child' table (InvoiceDetails), the selector should be the value for the foreign key that references the 'parent' table (Invoice).

DELETE FROM InvoiceDetails WHERE InvoiceDetails.InvoiceID='put.here.the.invoice.id'

3）从发票中删除发票行表

3) Delete the invoice row from the Invoice table

DELETE FROM Invoice WHERE Invoice.id_invoice='put.here.the.invoice.id'

4）提交（）交易。



如果出现错误（例外）， RollBack（）交易。



pd我假设您的发票表中有一个名为 id_invoice 的主键列，该列唯一标识发票和 InvoiceDetails 表中的一个名为 InvoiceID 的外键。

4) Commit() the transaction.

In case of error (exception), RollBack() the transaction.

p.d. I've assumed you have one primary-key column named id_invoice in your Invoice table that uniquely identifies the invoices and one foreign-key named InvoiceID in your InvoiceDetails table.

我假设有一个ID链接他们？可能有几种方法可以做到，但一个简单的方法就是：





从Invoice中删除Invoice_id =：Id;

从InvoiceDetail中删除Invoice_Id = Id;



简单且应该快速运行

I'm assuming there is an ID that links them? Probably a few ways to do it but a simple way is just:


Delete from Invoice where Invoice_id = :Id;
Delete from InvoiceDetail where Invoice_Id =Id;

Simple and should run quickly

这篇关于从Sql数据库中删除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！