I have the following code:
Begin tran
DECLARE @DATE DATETIME
SET @DATE = '2016/12/31 23:59:59'
DELETE FROM EntradaVTP
WHERE id in (
SELECT id
FROM Solicitud
WHERE Solicitud.FechaCreacion <= @DATE
)
But I also have to delete them from the table SalidaVTP and it's just the same, is there any way to unify? Something like this:
....
DELETE FROM EntradaVTP AND SalidaVTP...
WHERE id in (......
Thank you!
It's not possible to do it directly, but you can save the records to be deleted first (in a temporary table) and then delete the matching ones in the first table and then the ones matching the second one.
You could also with a single query, like so:
Here the records of both tables are brought through a
LEFT JOIN
in each of them and all the rows that come, both int1
and int2
, will meet the conditions required for theDELETE
. TheWHERE IN
in this case is not necessary, because the twoLEFT JOIN
already do their job.Anyway, to avoid doing this manually (even if it's through a function), consider applying type constraints
ON DELETE CASCADE
on your tables ( here's an example, also explained graphically, on how it works ), or consider programming aTRIGGER
in one of the tables to delete the records in the other(s).