January 7, 2016 at 12:38 pm
I have this complex delete statement.
I was wondering if there's a way to simply it? I tried formatting, but it still looks complex.
--delete all entries in the table 'exercises' that are in the subquery
delete from MissionStaging.mission.exercises where entryId in(
--select all entryIds in the subquery
select entryId from MissionStaging.mission.entries where missionID in(
--return all missionIds that are NOT also in the table 'shipList'
select missionId from MissionStaging.mission.missions where missionId not in(
--return all missionIds from the table shipList that are NOT null)
select missionid from MissionStaging.dbo.shipList where missionid is not null)))
Thanks!
January 7, 2016 at 12:54 pm
Magy (1/7/2016)
I have this complex delete statement.I was wondering if there's a way to simply it? I tried formatting, but it still looks complex.
--delete all entries in the table 'exercises' that are in the subquery
delete from MissionStaging.mission.exercises where entryId in(
--select all entryIds in the subquery
select entryId from MissionStaging.mission.entries where missionID in(
--return all missionIds that are NOT also in the table 'shipList'
select missionId from MissionStaging.mission.missions where missionId not in(
--return all missionIds from the table shipList that are NOT null)
select missionid from MissionStaging.dbo.shipList where missionid is not null)))
Thanks!
Use joins.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 7, 2016 at 1:43 pm
Thanks. I tried joins, but I am not receiving the same number of rows as I am with the original query.
I replace the delete with a select for testing:
SELECT r.*
FROM MissionStaging.mission.exercises r
JOIN MissionStaging.mission.entries e ON r.entryId = e.entryId
JOIN MissionStaging.mission.missions b ON e.missionId = b.missionId
LEFT JOIN MissionStaging.dbo.shipList m ON b.missionId = m.missionId
WHERE m.missionId IS NOT NULL
ChrisM@home (1/7/2016)
Magy (1/7/2016)
I have this complex delete statement.I was wondering if there's a way to simply it? I tried formatting, but it still looks complex.
--delete all entries in the table 'exercises' that are in the subquery
delete from MissionStaging.mission.exercises where entryId in(
--select all entryIds in the subquery
select entryId from MissionStaging.mission.entries where missionID in(
--return all missionIds that are NOT also in the table 'shipList'
select missionId from MissionStaging.mission.missions where missionId not in(
--return all missionIds from the table shipList that are NOT null)
select missionid from MissionStaging.dbo.shipList where missionid is not null)))
Thanks!
Use joins.
January 7, 2016 at 3:41 pm
When deleting or updating a set of rows based on IDs derived from other related tables, it can be helpful, for both performance and readability reasons, to first select the IDs into a temporary table, and then perform the delete in a separate operation.
-- Select all select all entryIds for which to delete:
select distinct entryId
into #EntryIdsForDelete
from MissionStaging.mission.entries
where missionID in
(
-- First, sub-select all missionIds that are NOT also in the table shipList:
select missionId from MissionStaging.mission.missions
where missionId is not null
except
select missionid from MissionStaging.dbo.shipList
);
-- Finally, delete all entries in the table Exercises where entryID is in #EntryIdsForDelete:
delete from MissionStaging.mission.exercises
where entryId in ( select entryId from #EntryIdsForDelete );
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 7, 2016 at 6:16 pm
Magy (1/7/2016)
Thanks. I tried joins, but I am not receiving the same number of rows as I am with the original query.I replace the delete with a select for testing:
SELECT r.*FROM MissionStaging.mission.exercises r
JOIN MissionStaging.mission.entries e ON r.entryId = e.entryId
JOIN MissionStaging.mission.missions b ON e.missionId = b.missionId
LEFT JOIN MissionStaging.dbo.shipList m ON b.missionId = m.missionId
WHERE m.missionId IS NOT NULL
It should be IS NULL in WHERE clause.
But it's better to use WHERE EXISTS for such checks:
SELECT r.*
FROM MissionStaging.mission.exercises r
JOIN MissionStaging.mission.entries e ON r.entryId = e.entryId
JOIN MissionStaging.mission.missions b ON e.missionId = b.missionId
WHERE NOT EXISTS (SELECT * FROM MissionStaging.dbo.shipList m
WHERE b.missionId = m.missionId )
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply