Deleting or updating two rows based on a value of one of them

  • Hi I need help in updating or deleting two rows based on the value of one of the columns.

    For example, please note the sample data I have provided.

    CheckNumber 20009, I have three rows. two of the rows have a value of 1 in removed.  In this case, I need to update to any value other than 0 or 1 the row where removed = 1 but also update or delete one more row where the values = 0.  So, of the three rows in this case, two will be deleted or updated but one will remain intact.

    For CheckNumber 20010, two rows will be deleted or updated but two will remain.

    For CheckNumber 20011, the two rows will be deleted or updated.

    CheckNumbers 20012 and 20013, will not be touched.

     

    The reason I need to do this is because the system creates one line with removed = 0 but, if users delete an item from an order, the system created another line where removed = 1 but does not update the first line created.

    I will appreciate any help on this.

    Thanks,

    Jose

     

    CREATE TABLE [dbo].[Checks](

    [StoreNumber] [int] NULL,

    [Date] [datetime] NULL,

    [ID] [int] NOT NULL,

    [CheckNumber] [int] NULL,

    [Voided] [tinyint] NOT NULL,

    [Removed] [tinyint] NULL,

    ) ON [PRIMARY]

    INSERT INTO [Checks]

    ([StoreNumber], [Date], [ID], [CheckNumber], [Voided], [Removed])

    SELECT 1,'2019-05-01 00:00:00.000','350', 20009, 0, 0 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','350', 20009, 0, 1 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','350', 20009, 0, 1 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','1500', 20010, 0, 0 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','1500', 20010, 0, 1 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','1500', 20010, 0, 0 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','1500', 20010, 0, 0 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','100', 20011, 0, 1 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','100', 20011, 0, 0 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','200', 20012, 0, 0 UNION ALL

    SELECT 1,'2019-05-01 00:00:00.000','200', 20013, 0, 0

    select * from checks

  • Providing a sample of what you would like the end result to look like would be very helpful here.  You aren't very specific about whether you want rows updated or deleted and if you want them updated what would you like it updated to?  I'm just going to take a guess but I feel like you basically want some of the rows to cancel each other out.  i.e. a row with removed 1 would zero out another row with removed 0 with the same ID.  However, if that's the case check number 20009 would be left with 1 row with a removed value of 1.  Which begs the question of how can you delete the same item twice?


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • I agree with Y.B. - expected results would make it much clearer what you're after.  For example, what do you mean by "deleted or updated"?

    Here's a guess.  It doesn't fit exactly with your narrative, but it might get you started.

    MERGE Checks AS tgt
    USING (
        SELECT
            CheckNumber
        ,   MAX(Removed)
        FROM Checks
        GROUP BY CheckNumber
        ) AS src (CheckNumber, Removed)
    ON tgt.CheckNumber = src.CheckNumber
    WHEN MATCHED AND tgt.Removed = 1
        THEN DELETE
    WHEN MATCHED
        THEN UPDATE
        SET tgt.Removed = 2;
  • I am sorry, what I mean by "delete or update" is I can use any of the options.  But, to be more specific, I need to update Removed = 2 where one of the rows removed = 1.

    Thanks,

     

  • Required me three attempts to read the original post 😉 I think, he means the input data may be errorous, as click streams can be. Hence, I suggest to perform some cleansing in a very first step. Either using a view or a CTE. Knowing very little about the rest, I assume you want to preserve the information that there was a cancelled order.

    Can't provide an update/merge statement from scratch, however I think the result should look like this:

    with rawData as
    (
    select
    CheckNumber,
    [Date],
    Removed,
    row_number() over(
    partition by
    CheckNumber,
    Removed
    order by
    Removed,
    [Date]
    ) seq
    from dbo.checks
    ),

    cleaned as
    (
    select
    rd.CheckNumber,
    rd.[Date],
    case
    when rd.Removed = 0 and lead(rd.Removed) over(partition by rd.CheckNumber order by rd.[Date]) = 1 then 1
    when rd.Removed = 0 then 0
    end as Removed
    from rawData rd
    where
    (rd.Removed = 0) or (rd.Removed = 1 and seq = 1)
    )

    select *
    from cleaned
    where
    Removed is not null

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply