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

Question

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

Answer 1

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)