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
mercybthomas74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
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
0
Kevin CrossChief Technology OfficerCommented:
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

0
mercybthomas74Author Commented:
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
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

mimran18Commented:
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)
0
Kevin CrossChief Technology OfficerCommented:
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')
      )
0
David ToddSenior Database AdministratorCommented:
Hi,

Based on your code, but adding another test case ...

HTH
  David
if object_id( N'tempdb..#Test_STD_Choice_MT', N'U' ) is not null 
	drop table #Test_STD_Choice_MT;
	
go

create table #Test_STD_Choice_MT(
	ID varchar(6)
	, Choice int
	, School varchar(6)
	)

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

-- adding another choice, should be deleted
insert #Test_STD_Choice_MT values( '006', 3, '025' )

select * from #Test_STD_Choice_MT

print 'ID''s to keep'
select ID
from #Test_STD_Choice_MT
where 
	School = '025'
	and ( Choice = 1 or Choice = 2 )
;

print 'rows to delete'
select o.*
from #Test_STD_Choice_MT o
where
	o.ID not in (
		select i.ID
		from #Test_STD_Choice_MT i
		where 
			i.School = '025'
			and ( i.Choice = 1 or i.Choice = 2 )
		)
;

print 'delete'
delete o
from #Test_STD_Choice_MT o
where
	o.ID not in (
		select i.ID
		from #Test_STD_Choice_MT i
		where 
			i.School = '025'
			and ( i.Choice = 1 or i.Choice = 2 )
		)
;

print 'Results'
select * from #Test_STD_Choice_MT
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.