asked on

sql script - delete where no exists

create delete statement

how to check if the student are in school 025 either in choice 1 or 2;
if the student is in another schools other than 025 , it should nbe deleted

for e.g.: check the excel sheet
Stud-ID: 003 needs to be deleted from the list

I tried to use delete query

DELETE FROM Tbl
WHERE CHOICE = 2
AND NOT EXISTS
(
SELECT STD_ID, CHOICE, PGM_LOC
FROM #TMP_MATAPPS TMP1
WHERE TMP1.STD_ID = STD_ID
AND TMP1.PGM_LOC = PGM_LOC
AND TMP1.CHOICE = 1
)

Please help
test.xlsx

Kent Dyer

Before you dive into your delete query.. Re-write to use SELECT first.. Once you nail it down, then convert to DELETE..

DELETE FROM Tbl
      WHERE CHOICE = 2
      AND NOT EXISTS
      (
            SELECT STD_ID, CHOICE, PGM_LOC
            FROM #TMP_MATAPPS TMP1
            WHERE TMP1.STD_ID = STD_ID
            AND TMP1.PGM_LOC = PGM_LOC
            AND TMP1.CHOICE = 1
      )

Open in new window

Re-Write as:

SELECT TOP 100* FROM Tbl
      WHERE CHOICE = 2
      AND NOT IN
      (
            SELECT STD_ID, CHOICE, PGM_LOC
            FROM #TMP_MATAPPS TMP1
            WHERE TMP1.STD_ID = STD_ID
            AND TMP1.PGM_LOC = PGM_LOC
            AND TMP1.CHOICE = 1
      )

Open in new window

Once you get the SELECT nailed down, then convert DELETE.. Note: I changed to AND NOT IN..

HTH,

Kent

Kevin Cross

Try this:
(looks for Choice = 1 OR any row with School = '025')

DELETE FROM Tbl
      WHERE CHOICE = 2
      AND NOT EXISTS
      (
            SELECT 1
            FROM #TMP_MATAPPS TMP1
            WHERE TMP1.STD_ID = STD_ID
            AND TMP1.PGM_LOC = PGM_LOC
            AND (TMP1.CHOICE = 1 OR TMP1.SCHOOL = '025')
      ) 
;

Open in new window

mercybthomas74

ASKER

None of the script worked: It did not delete the Stud ID 003 and the rest should remain as the final results

DROP TABLE dbo.Test_STD_Choice_MT
GO
CREATE TABLE dbo.Test_STD_Choice_MT
(
ID            varchar(6)
,Choice            int
,School            varchar(6)
)

INSERT INTO dbo.Test_STD_Choice_MT VALUES ('001', 1, '025')
INSERT INTO dbo.Test_STD_Choice_MT VALUES ('001', 2, '009')
INSERT INTO dbo.Test_STD_Choice_MT VALUES ('002', 1, '025')
INSERT INTO dbo.Test_STD_Choice_MT VALUES ('002', 2, '009')
INSERT INTO dbo.Test_STD_Choice_MT VALUES ('003', 2, '134')
INSERT INTO dbo.Test_STD_Choice_MT VALUES ('004', 1, '025')
INSERT INTO dbo.Test_STD_Choice_MT VALUES ('005', 2, '025')

select * from Test_STD_Choice_MT

ID      Choice      School
001      1      025
001      2      009
002      1      025
002      2      009
003      2      134
004      1      025
005      2      025
---------------------------------------------------------------
SELECT * FROM Test_STD_Choice_MT
WHERE Choice = 2
AND School NOT IN
(
SELECT School
FROM Test_STD_Choice_MT TMP1
WHERE TMP1.ID = ID
AND TMP1.School = School
AND TMP1.Choice = 1
)

ID      Choice      School
001      2      009
002      2      009
003      2      134
------------------------------------------------------------------
DELETE FROM Test_STD_Choice_MT
WHERE Choice = 2
AND NOT EXISTS
(
SELECT 1
FROM Test_STD_Choice_MT TMP1
WHERE TMP1.ID = ID
AND TMP1.School = School
AND (TMP1.Choice = 1 OR TMP1.School = '025')
)
select * from Test_STD_Choice_MT
ID      Choice      School
001      1      025
001      2      009
002      1      025
002      2      009
003      2      134
004      1      025
005      2      025

mimran18

Here we go.

Delete from Test_STD_Choice_MT Where [ID] IN (Select [ID] from Test_STD_Choice_MT
Group By [ID]
Having SUM(Case When School ='025' then 1 else 0 end)=0)

Kevin Cross

You have to get rid of AND TMP1.School = School

DELETE FROM Test_STD_Choice_MT
WHERE Choice = 2
AND NOT EXISTS
(
SELECT 1
FROM Test_STD_Choice_MT TMP1
WHERE TMP1.ID = ID
AND (TMP1.Choice = 1 OR TMP1.School = '025')
)

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial