I have a table tblPhone
which contains duplicate records. E.g.
x
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;