I have table with three columns: contractID, customergroup, customerID. Some data for customerID is missing, and I need to find a way to replace those NULL values with customerID in corresponding customergroup. Also, if no customerID is found for the customergroup, then leave cell as blank.

Example:

Expected:

CodePudding user response：

You may use a sub-query to fill the missing ids as the following:

SELECT contractID, customergroup,
       CASE WHEN customerID IS NULL THEN
       COALESCE((SELECT customerID FROM table_name D 
       WHERE D.customergroup = T.customergroup AND 
       D.customerID IS NOT NULL 
       LIMIT 1), '')
       ELSE customerID 
       END AS customerID
FROM table_name T
ORDER BY contractID

See a demo on MySQL.

CodePudding user response：

Select contractID , customergroup, ISNULL(customerID, ' ') as customerID
From tablename

In the blank space ' ' you can enter the value you want