May 3, 2019 at 2:17 pm
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
May 3, 2019 at 3:05 pm
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?
May 3, 2019 at 3:09 pm
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;
May 3, 2019 at 3:16 pm
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,
May 7, 2019 at 3:44 pm
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