CODEWITHSUNDEEP
sqlsql-servert-sql
H22
H22

Reputation: 169

Converting a delete statement into a where clause, T-sql

I have removed rows from a results table after it has been built already. I have decided to try to remove the rows from being inserted into the results table in the first place instead.

To remove the appropriate rows from the results table after the fact I used:

if @InterchangeAction = 'HCR'
        begin

        --Do not allow claims to be output if they have a prior submission marked 'output'
        --and the interchange 'output submission action' is marked as 'hold'
            delete from #ResultSet
             where exists ( select 1 from ClaimSubmissions CS inner join InterchangeInfo I on CS.InterchangeId = I.InterchangeId
                             where #ResultSet.ClaimId = CS.ClaimId
                             and CS.InterchangeId = @InterchangeID
                              and CS.SubmissionStatus = 'OPT'
                              and CS.OutputDate is not NULL
                          )
        end

This works as I want but I am thinking it would be more efficient to stop the rows from being added in the first place.

I'm going to start my check with:

if @InterchangeAction = 'HCR'

and then concatenate on to the existing where clause but I am unsure on how to convert the delete statement into a where clause?

Any pointers on where to start would be greatly appreciated.

Upvotes: 0

Views: 70

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

Unless I'm missing something obvious, aren't you just looking for this?:

INSERT #ResultSet 
  (<Column List>)
SELECT 
  <Column List>
FROM 
  WhatHaveYou AS WHY
WHERE NOT EXISTS ( select 1 from ClaimSubmissions CS 
                            inner join InterchangeInfo I 
                                on CS.InterchangeId = I.InterchangeId
                             where WHY.ClaimId = CS.ClaimId
                              and CS.InterchangeId = @InterchangeID
                              and CS.SubmissionStatus = 'OPT'
                              and CS.OutputDate is not NULL
                          )

Upvotes: 1

Related Questions