locked
delete all related rows if one row is null RRS feed

  • Question

  • Hi,

    I have a table like following ABC, if enddate or startdate or SKU is empty or null, we need to delete all rows have same UploadedTime.

    I'm trying to use delete from where, but only bad records deleted, not all. Thank you for any help.

    CREATE TABLE [dbo].[ABC](
    	[SKU] [varchar](50) NOT NULL,
    	[StartDate] [date] NOT NULL,
    	[EndDate] [date] NULL,
    	[UploadedTime] [datetime] NULL,
     ) ON [PRIMARY]

    Tuesday, February 21, 2012 9:14 PM

Answers

  • ;
    WITH cte as
    (
        SELECT UploadedTime
        FROM [dbo].[ABC]
        WHERE EndDate is null or SKU='' or StartDate ='19000101'
    )
    DELETE [dbo].[ABC] FROM cte
    INNER JOIN [dbo].[ABC] a
    ON cte.UploadedTime=a.UploadedTime
    Since neither SKU nor StartDate can be NULL the check is modified for empty strings or a Zero value (for StartDate).
    • Edited by LMU92 Tuesday, February 21, 2012 9:33 PM query fixed
    • Marked as answer by memostone Tuesday, February 21, 2012 9:38 PM
    Tuesday, February 21, 2012 9:29 PM
  • In the simplest case:

    DELETE
        ABC
    WHERE
        UploadedTime = (SELECT DISTINCT UploadedTime FROM ABC WHERE EndDate IS NULL)



    • Edited by Brian Tkatch Tuesday, February 21, 2012 9:40 PM
    • Marked as answer by memostone Tuesday, February 21, 2012 9:55 PM
    Tuesday, February 21, 2012 9:40 PM

All replies

  • ;
    WITH cte as
    (
        SELECT UploadedTime
        FROM [dbo].[ABC]
        WHERE EndDate is null or SKU='' or StartDate ='19000101'
    )
    DELETE [dbo].[ABC] FROM cte
    INNER JOIN [dbo].[ABC] a
    ON cte.UploadedTime=a.UploadedTime
    Since neither SKU nor StartDate can be NULL the check is modified for empty strings or a Zero value (for StartDate).
    • Edited by LMU92 Tuesday, February 21, 2012 9:33 PM query fixed
    • Marked as answer by memostone Tuesday, February 21, 2012 9:38 PM
    Tuesday, February 21, 2012 9:29 PM
  • thank you LMU92
    Tuesday, February 21, 2012 9:38 PM
  • In the simplest case:

    DELETE
        ABC
    WHERE
        UploadedTime = (SELECT DISTINCT UploadedTime FROM ABC WHERE EndDate IS NULL)



    • Edited by Brian Tkatch Tuesday, February 21, 2012 9:40 PM
    • Marked as answer by memostone Tuesday, February 21, 2012 9:55 PM
    Tuesday, February 21, 2012 9:40 PM