• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

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
0
mercybthomas74
Asked:
mercybthomas74
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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 DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now