SQL Delete with Sub-Query

[clamum]

I have a SQL query below:

SELECT us.GIN, us.SegmentID
FROM tblUserSegment us
INNER JOIN tblSegmentGroup sg on us.SegmentID = sg.SegmentGroupID
INNER JOIN tblUser u on us.GIN = u.PersonnelNumber
WHERE (sg.BusinessCategoryCodes IS NOT NULL AND sg.BusinessCategoryCodes != '')
    AND (sg.JobCodes IS NOT NULL AND sg.JobCodes != '')
    AND (CHARINDEX(u.BusinessArea, sg.BusinessCategoryCodes) = 0 OR CHARINDEX(u.JobCode, sg.JobCodes) = 0)

This returns a number of records from tblUserSegment. These are the records I wish to delete from tblUserSegment as well. I originally had the following query to do so:

DELETE FROM tblUserSegment
WHERE GIN IN (
    SELECT us.GIN
    FROM tblUserSegment us
    INNER JOIN tblSegmentGroup sg on us.SegmentID = sg.SegmentGroupID
    INNER JOIN tblUser u on us.GIN = u.PersonnelNumber
    WHERE (sg.BusinessCategoryCodes IS NOT NULL AND sg.BusinessCategoryCodes != '')
        AND (sg.JobCodes IS NOT NULL AND sg.JobCodes != '')
	AND (CHARINDEX(u.BusinessArea, sg.BusinessCategoryCodes) = 0 OR CHARINDEX(u.JobCode, sg.JobCodes) = 0)
)

The problem with the delete query above, it was deleting users it should have, but also users that were in other segments besides from the segment's SegmentID that you see in the first query I posted, since a user's GIN could be tied to SegmentID=1 and SegmentID=36. I don't think this is too difficult but I'm just kind of having trouble with the proper logic. I don't think I can use the "WHERE GIN IN ( sub-query )" logic, and I tried the "WHERE EXISTS ( sub-query )" format but that did not work. Perhaps a join back to tblUserSegment is needed?

Any pointers? Thanks a bunch.

 

[gimmewhitecastles]

First query has
SELECT us.GIN, us.SegmentID

2nd query has only
SELECT us.GIN

Seems like the 1st query is more specific.

 

[Ka0t1x]

SQL Sub-selects should only return one item.

Maybe I'm tired but I see no difference other than an additional column, from the same table. Which should be fine.

 

[KLin]

BEGIN TRAN
DELETE tblUserSegment
FROM tblUserSegment us
INNER JOIN tblSegmentGroup sg on us.SegmentID = sg.SegmentGroupID
INNER JOIN tblUser u on us.GIN = u.PersonnelNumber
WHERE (sg.BusinessCategoryCodes IS NOT NULL AND sg.BusinessCategoryCodes != '')
AND (sg.JobCodes IS NOT NULL AND sg.JobCodes != '')
AND (CHARINDEX(u.BusinessArea, sg.BusinessCategoryCodes) = 0 OR CHARINDEX(u.JobCode, sg.JobCodes) = 0)
ROLLBACK TRAN

Always wrap action queries in a transaction and test with a rollback tran first. 🙂

 

[clamum]

Ah man, forgot about this thread. I ended up using a common table expression like this to do it:

WITH delUsers (GIN, SegmentID)
AS (
    SELECT us.GIN, us.SegmentID
    FROM tblUserSegment us
    INNER JOIN tblSegmentGroup sg on us.SegmentID = sg.SegmentGroupID
    INNER JOIN tblUser u on us.GIN = u.PersonnelNumber
    WHERE (sg.BusinessCategoryCodes IS NOT NULL AND sg.BusinessCategoryCodes != '')
    AND (sg.JobCodes IS NOT NULL AND sg.JobCodes != '')
    AND (CHARINDEX(u.BusinessArea, sg.BusinessCategoryCodes) = 0 OR CHARINDEX(u.JobCode, sg.JobCodes) = 0)
)
DELETE tblUserSegment
FROM tblUserSegment us
INNER JOIN delUsers cte on us.GIN = cte.GIN AND us.SegmentID = cte.SegmentID

Thanks for the help guys!

 

[Evadman]

delete from tblUserSegment 
FROM tblUserSegment us
INNER JOIN tblSegmentGroup sg on us.SegmentID = sg.SegmentGroupID
INNER JOIN tblUser u on us.GIN = u.PersonnelNumber
WHERE (sg.BusinessCategoryCodes IS NOT NULL AND sg.BusinessCategoryCodes != '')
    AND (sg.JobCodes IS NOT NULL AND sg.JobCodes != '')
    AND (CHARINDEX(u.BusinessArea, sg.BusinessCategoryCodes) = 0 OR CHARINDEX(u.JobCode, sg.JobCodes) = 0)