I am new to using SQL queries on SQL Server, and hence I have met a roadblock. I have a table in the given format

However I want to replace the null values in TYPE column with matching values from CLASS_NAME and DESCRIPTION. Example: When Class_Name = A and Description = School & TYPE IS NULL, then use the TYPE value where CLASS_NAME , TYPE & DESCRIPTION ARE NOT NULL (in this case TYPE = S).

Please feel free to ask for more demonstration if needed. Any help would be appreciated. Thanks

CodePudding user response：

UPDATE yourTable SET 
    TYPE=U.[TYPE]
FROM (
SELECT DISTINCT T.[CLASS_NAME], T.[TYPE], T.[DESCRIPTION] 
FROM yourTable T
WHERE T.[TYPE] IS NOT NULL AND T.[DESCRIPTION] IS NOT NULL AND T.[CLASS_NAME] IS NOT NULL
) U 
WHERE 
    yourTable.[TYPE] IS NULL 
    AND U.[CLASS_NAME]=yourTable.[CLASS_NAME] 
    AND U.[DESCRIPTION]=yourTable.[DESCRIPTION]

But first check that your data is unambiguous:

SELECT [CLASS_NAME], [TYPE], [DESCRIPTION], COUNT(*)
FROM 
    yourTable
WHERE
    [CLASS_NAME] IS NOT NULL AND 
    [TYPE] IS NOT NULL AND
    [DESCRIPTION] IS NOT NULL
GROUP BY 
    [CLASS_NAME], 
    [TYPE], 
    [DESCRIPTION]
HAVING 
    COUNT(*) > 1

When above query returns any row - you have to replace ambiguous incorrect values and then run first query.

By "ambiguous" I mean the situation that there are more than one value of TYPE for the same CLASS_NAME and DESCRIPTION.

CodePudding user response：

Try this

    SELECT a.CLASS_NAME, a.DESCRIPTION, b.TYPE FROM TABLEA a 
    JOIN (SELECT CLASS_NAME, DESCRIPTION, TYPE from TABLEA where TYPE IS 
    NOTNULL) b on a.CLASS_NAME == b.CLASS_NAME and a.DESCRIPTION == 
    b.DESCRIPTION