Update a Null field to the value of the same field of the same group 2

[LittleNick]

Hi Everyone,
I have a table with a field that has null value that I wan to update. Let say I have a table tblCustomer:

OrderID Customer# CustomerName
10890 2234 John Smith
10890 Null John Smith
10890 Null John Smith
16789 9978 Mary Jane
16789 Null Mary Jane
26780 13768 Paul Young
26780 Null Paul Young
33245 2234 John Smith
33245 Null John Smith

and I want to update the Customer# so that all the 10890 and 33245 OrderID have 2234 as Customer#, and 9978 for 16789 OrderID instead of null and so forth.

Thanks for helps

 

[markros]

;with cte as (select * from tblCustomer where [Customer#] is not null)

update tblCustomer set [Customer#] = c.[Customer#]
from tblCustomer T inner join cte c on T.OrderID = c.OrderID
where T.[Customer#] IS NULL

PluralSight Learning Library

 

[bborissov]

UPDATE Customer Set CustomerId = Tbl1.CustomerId
FROM Customer
INNER JOIN Customer Tbl1 
      ON Customer.OrderId = Tbl1.OrderId AND
         Tbl1.CustomerId IS NOT NULL
WHERE Customer.CustomerId IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[LittleNick]

It works! Thanks markros and bborissov