CODEWITHSUNDEEP
sqlsql-server
Pat123
Pat123

Reputation: 13

How to delete only rows that comply with condition

I have the query below and the select return the rows that I want to delete but when I ran the entire query oh boy it deleted everything from the table.

How do I change the delete to only delete the rows returned by the select after the where:

delete  from StaffSecurityItems
  WHERE exists (
    SELECT  ssi.SysSaffID
  FROM StaffDeparment sd, CustomFieldValues cfv, StaffSecurityItems ssi
  where cfv.SysCustomFieldID = '-9223372036854749962'
  and cfv.FieldValue = 'Yes'
    and sd.SysStaffRoleID =11
  and cfv.SysDeparmentID = sd.SysDeparmentID
   and ssi.SysSaffID = SysSaffID
    and ssi.ItemName in ('EnrollNewMember','EditEnrollmentInfo')
    and ssi.SysStudyID ='-9223372036854759558');

My main issues is with the delete, I need to make sure that in only deletes what is being returned by select.

Upvotes: 0

Views: 53

Answers (1)

Thom A
Thom A

Reputation: 96016

If the SELECT works, why not simply replace the SELECT? What you have there is a query that will delete every row in the table StaffSecurityItems if the query within the EXISTS returns even a single row (what rows it finds is meaningless, due to a lack of a correlated query).

DELETE SD
FROM StaffDeparment sd
     JOIN CustomFieldValues cfv ON cfv.SysDeparmentID = sd.SysDeparmentID
     JOIN StaffSecurityItems ssi ON ssi.SysSaffID = sd.SysSaffID --guessed alias
WHERE cfv.SysCustomFieldID = '-9223372036854749962'
  AND cfv.FieldValue = 'Yes'
  AND sd.SysStaffRoleID =11
  AND ssi.ItemName in ('EnrollNewMember','EditEnrollmentInfo')
  AND ssi.SysStudyID ='-9223372036854759558'

And, as mentioned in the comments, the ANSI-92 syntax has been around for 27 years! There's no reason you shouldn't be using it: Bad habits to kick : using old-style JOINs

Upvotes: 3

Related Questions