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
Notecolumn which hasNULLvalue in first row andValusome values in second row. Question 2 is: How I can update the value from filled cellValuinto empty/NULL cell?Question 3 is: How I can take and update the lowest number value in each cell in
Classcolumn?
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;