SQL Server Set value in a column from the same column in another row in a group

y1aodyip 于 2023-05-05 发布在其他

关注(0)|答案(1)|浏览(195)

I have a select like this:

SELECT Number 
FROM customers
GROUP BY Number
HAVING COUNT(*) > 1 
   AND SUM(CASE WHEN customerID IS NULL THEN 1 END) > 0
   AND SUM(CASE WHEN customerID IS NOT NULL THEN 1 END) > 0

Each group contains at least one row with customerID = null and at least one with customerID is not null.

If in group more than one rows with customerID is not null that ID`s is identical

I need an update which set customerID to rows with customerID = null from rows with customerID is not null.

How to do it? I probably did not quite correctly describe, my select returns the following lines:
| Number | Customer id |
| ------------ | ------------ |
| 6720-7337-7464-2154 | 1167 |
| 6720-7337-7464-2154 | 1167 |
| 6720-7337-7464-2154 | NULL |
| 9543-2478-3326-1189 | 1235 |
| 9543-2478-3326-1189 | NULL |
| 9543-2478-3326-1189 | NULL |

And i need an update which make something like:

Number	Customer id
6720-7337-7464-2154	1167
6720-7337-7464-2154	1167
6720-7337-7464-2154	1167
9543-2478-3326-1189	1235
9543-2478-3326-1189	1235
9543-2478-3326-1189	1235

sql-server

来源：https://stackoverflow.com/questions/76085827/set-value-in-a-column-from-the-same-column-in-another-row-in-a-group

1条答案

按热度按时间

64jmpszr1#

you can use window function and Cte

then update all customers with value is null

;with  _list as (
       
select 
        number
        ,customerID
        ,max(customerID)
over(partition by number  ) as maxcustomerID
                 

from customers 
)

update  _list
set customerID=maxcustomerID
where customerID is null

You can create insert base data with the following statements:

drop table customers
create table customers(number varchar(100),customerID int)

insert into customers(number,customerID)values('6720-7337-7464-2154',   1167)
insert into customers(number,customerID)values('6720-7337-7464-2154',   1167)
insert into customers(number,customerID)values('6720-7337-7464-2154',   NULL)
insert into customers(number,customerID)values('9543-2478-3326-1189',   1235)
insert into customers(number,customerID)values('9543-2478-3326-1189',   NULL)
insert into customers(number,customerID)values('9543-2478-3326-1189',   NULL)

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server Set value in a column from the same column in another row in a group

1条答案

相关问题

热门标签

最新问答