Skip to content
Advertisement

Updating empty cells with filled cells values in duplicate records without unique key

I have a table tblPhone which contains duplicate records. E.g.

Group    First_Name    Middle_Name    Last_Name    Class    ...    Note    ...Other_Columns
-----    ----------    -----------    ---------    -----    ...    ----    ...-------------
Avo      FirstName     MiddleName     LastName     246      ...    NULL    ...Other_Columns
Avo      FirstName     MiddleName     LastName     215      ...    Valu    ...Other_Columns

As you are seeing above sample table have two duplicate records without uniquely identified KEY.

  1. Question 1 is: How I can uniquely identify each duplicate record in table?

  2. See the Note column which has NULL value in first row and Valu some values in second row. Question 2 is: How I can update the value from filled cell Valu into empty/NULL cell?

  3. Question 3 is: How I can take and update the lowest number value in each cell in Class column?

The UPDATE query that I tried to write is below but confused in CASE ELSE clauses:

UPDATE tblPhone
   SET [Note] = CASE WHEN [Note] = '' THEN [Note] = ???
WHERE Last_Name = 'LastName'
  AND First_Name = 'FirstName';

I have also created sample table schema here in SQL Fiddle.

Database is created in SQL Server 2017.

Advertisement

Answer

You can use a subquery:

UPDATE p1
    SET p1.[Note] = (
        SELECT TOP(1) [Note]
        FROM tblPhone p2 
        WHERE p2.[Note] IS NOT NULL 
            AND p2.First_Name = p1.First_Name
            AND p2.Last_Name = p1.Last_Name
        )
FROM tblPhone p1
WHERE p1.[Note] IS NULL;
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement