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.
Question 1 is: How I can uniquely identify each duplicate record in table?
See the
Note
column which hasNULL
value in first row andValu
some values in second row. Question 2 is: How I can update the value from filled cellValu
into empty/NULL cell?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;