CODEWITHSUNDEEP
sql-servert-sql
Dominic
Dominic

Reputation: 175

update field from group by

In SQL Server 2016, let's have the following recordset

enter image description here

I need to update the "IsSame" field when the tpEndDay is the same for any arbDelivery field. For example, arbDelivery 76873614 should have IsSame = 0, because tpEndDay is different. 76874450 shoud have IsSame = 1, etc.

How can I do this?

thanks for your time and help

Upvotes: 0

Views: 44

Answers (3)

Yair Maron
Yair Maron

Reputation: 1948

I always recommend to specify FROM with the same table you UPDATE.

in this case you can do it like this:

UPDATE  my_table
SET     IsSame = 
            CASE
                WHEN EXISTS
                (
                    SELECT  1
                    FROM    my_table AS same
                    WHERE   same.tpEndDay = my_table.tpEndDay
                    AND     same.arbDelivery <> my_table.arbDelivery
                )
                THEN 1
                ELSE 0
            END
FROM    my_table

Upvotes: 1

Charlieface
Charlieface

Reputation: 71544

You can use a COUNT window aggregate in a derived table and update directly through the derived table

UPDATE t
    SET IsSame = CASE WHEN t.cnt > 1 THEN 1 ELSE 0 END
FROM (
    SELECT *
        cnt = COUNT(*) OVER (PARTITION BY arbDelivery)
    FROM YourTable
) t

Upvotes: 0

Gudwlk
Gudwlk

Reputation: 1157

I added some sample data. You can change the On clause according to your join condition.

CREATE TABLE [dbo].[mydatatable](
    [arbNoColis] [varchar](100) NULL,
    [arbDelivery] [int] NULL,
    [tbEndDate] [datetime] NULL,
    [IsSame] [bit] NULL
  ) ON [PRIMARY]

 GO
   INSERT [dbo].[mydatatable] ([arbNoColis], [arbDelivery], [tbEndDate], [IsSame]) VALUES (N'wwwwwwwww', 24499002, CAST(N'2021-02-05T00:00:00.000' AS DateTime), 0)
GO
   INSERT [dbo].[mydatatable] ([arbNoColis], [arbDelivery], [tbEndDate], [IsSame]) VALUES (N'wwwwwwwww', 244990502, CAST(N'2021-05-05T00:00:00.000' AS DateTime), 1)
GO
  INSERT [dbo].[mydatatable] ([arbNoColis], [arbDelivery], [tbEndDate], [IsSame]) VALUES (N'tttttt', 244999002, CAST(N'2021-05-05T00:00:00.000' AS DateTime), 1)
 GO
 INSERT [dbo].[mydatatable] ([arbNoColis], [arbDelivery], [tbEndDate], [IsSame]) VALUES (N'tttttt', 222999002, CAST(N'2021-05-05T00:00:00.000' AS DateTime), 1)
 GO
 
UPDATEA
    SET A.IsSame = CASE WHEN B.[rowcount] = 1 THEN 0 ELSE 1 END
FROM mydatatable A 
INNER JOIN (

        SELECT
            COUNT(1) OVER (PARTITION BY tbEndDate ORDER BY tbEndDate) AS [rowcount]
            ,*
        FROM mydatatable ) AS B 
ON A.arbNoColis = B.arbNoColis AND A.arbDelivery = B.arbDelivery

Result

Upvotes: 0

Related Questions