none
Delete syntax without using correlated subqueries

    Question

  • Hi, 

    I have a below query and want to modify it without using co-related sub-query.

    DELETE FROM dbo.ReportingAuth
    WHERE ReportingEmailSeqId IN ( SELECT DISTINCT ReportingEmailSeqId 
      FROM dbo.ReportingEmail re 
      WHERE re.EmailAddress NOT IN ( SELECT DISTINCT Email 
     FROM [dbo].[AuthorizationStage]) 
      AND re.ReportingEmailSeqId !=0
    )


    I do not mind using outer joins but want to simple JOIN's and get the same result.

    Thanks,



    Krishna



    Friday, October 18, 2013 9:37 AM

Answers

  • Hello Krishna,

    This script

    -- DELETE FROM RA
    SELECT RA.*
    FROM dbo.ReportingAuth AS RA
         INNER JOIN dbo.ReportingEmail AS RE
             ON RA.ReportingEmailSeqId = RE.ReportingEmailSeqId
         LEFT JOIN [dbo].[AuthorizationStage] AS AU
             ON RE.EmailAddress = AU.Email
    WHERE RE.ReportingEmailSeqId !=0
          AND AU.EMail IS NULL

    should do the work; but it's untested, so please check the result as a SELECT statement before you run the DELETE!

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 18, 2013 9:49 AM

All replies

  • Hello Krishna,

    This script

    -- DELETE FROM RA
    SELECT RA.*
    FROM dbo.ReportingAuth AS RA
         INNER JOIN dbo.ReportingEmail AS RE
             ON RA.ReportingEmailSeqId = RE.ReportingEmailSeqId
         LEFT JOIN [dbo].[AuthorizationStage] AS AU
             ON RE.EmailAddress = AU.Email
    WHERE RE.ReportingEmailSeqId !=0
          AND AU.EMail IS NULL

    should do the work; but it's untested, so please check the result as a SELECT statement before you run the DELETE!

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 18, 2013 9:49 AM
  • Hi,

    I've had to make this up, so don't know if it works.  Please post some DDL and sample data next time - makes it much easier and quicker to come up with the correct solution.

    MERGE INTO dbo.ReportingAuth A
    USING
    	(
    		SELECT ra.ReportingEmailSeqID FROM dbo.ReportingAuth ra
    		JOIN dbo.ReportingEmail re
    			ON ra.ReportingEmailSeqId = re.ReportingEmailSeqId
    		LEFT JOIN dbo.AuthorizationStage ast
    			ON re.EmailAddress = ast.Email
    		WHERE re.ReportingEmailSeqID !=0
    	) AS SRC
    	ON src.ReportingEmailSeqID = a.ReportingEmailSeqID
    WHEN MATCHED THEN
    	DELETE
    OUTPUT $ACTION, DELETED.ReportingEmailSeqID AS deletedRESID;

    Thanks, Andrew
    My blog...


    • Edited by Andrew Bainbridge Friday, October 18, 2013 10:00 AM left out the where clause
    Friday, October 18, 2013 9:57 AM
  • I would rewrite your statement, but would not revert to the proprietary DELETE .. FROM syntax.

    How about this:

    DELETE FROM dbo.ReportingAuth
    WHERE EXISTS (
      SELECT *
      FROM      dbo.ReportingEmail     AS re 
      LEFT JOIN dbo.AuthorizationStage AS as
        ON  as.Email = re.EmailAddress
      WHERE re.ReportingEmailSeqId = ReportingAuth.ReportingEmailSeqId
      AND   re.ReportingEmailSeqId <> 0
      AND   as.Email IS NULL
    )
    

    Personally, I would go with

    DELETE FROM dbo.ReportingAuth
    WHERE EXISTS (
      SELECT *
      FROM   dbo.ReportingEmail     AS re 
      WHERE  re.ReportingEmailSeqId = ReportingAuth.ReportingEmailSeqId
      AND    re.ReportingEmailSeqId <> 0
      AND NOT EXISTS (
        SELECT *
        FROM   dbo.AuthorizationStage AS as
        WHERE  as.Email = re.EmailAddress
      )
    )


    Gert-Jan

    Friday, October 18, 2013 3:03 PM