Delete records from one table if they do not exist in another

Question

Delete records from one table if they do not exist in another

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

Friends good morning, I have the following problem.

In my database I have two tables (HeadingsFacturas and DetalleFacturas) both are joined by an IDFactura.

So for each invoice header, there is a detail that contains the invoiced transactions. So far so good.

What I need is validity that all the records in the DetailFacturas have a Header associated to it, so I need to make a comparison, since previously someone deleted some headings and there are some details that no longer have an associated header.

I hope you have given me to understand

sql-server

asked by Gio Gómez 20.04.2018 в 19:46

source

2 answers

1

I think you can leave with a join, I share this is very helpful.

answered by 20.04.2018 в 21:40

MVC problems pointing addresses Any other way to code Base 64 on Android?

score 2 · Accepted Answer

Sounds like a simple NOT EXISTS :

SELECT *
FROM dbo.DetalleFacturas DF
WHERE NOT EXISTS(SELECT 1 FROM dbo.EncabezadosFacturas
                 WHERE IdFactura = DF.IdFactura)
;

To delete these rows from the table, just:

DELETE
FROM dbo.DetalleFacturas DF
WHERE NOT EXISTS(SELECT 1 FROM dbo.EncabezadosFacturas
                 WHERE IdFactura = DF.IdFactura)
;