April 11, 2014 at 12:40 am
Create Table #Temp (ID int not null primary Key, Name varchar(25) not null, DOB datetime not null, Sex char(1), Race char(1), Height int, Weight int)
insert #Temp
select 1, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', NULL,NULL, NULL, NULL
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
union
select 4, 'Ryan', '1980-07-05 00:00:00.000', 'M','W', 507, 145
union
select 5, 'Robert', '1988-09-14 00:00:00.000', NULL,'B', 601, NULL
union
select 6, 'Robert', '1988-09-14 00:00:00.000', 'M',NULL, NULL, 190
union
select 7, 'Keith', '1986-09-05 00:00:00.000', 'M','C', 600, NULL
select * from #Temp
/* Expecting Output */
select 1 as ID, 'Kenneth' as Name, '1963-02-26 00:00:00.000' as DOB, 'M' as Sex,'C' as Race, 516 as Height, 160 as Weight
union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160
union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223
union
select 4, 'Ryan', '1980-07-05 00:00:00.000', 'M','W', 507, 145
union
select 5, 'Robert', '1988-09-14 00:00:00.000', 'M','B', 601, 190
union
select 6, 'Robert', '1988-09-14 00:00:00.000', 'M','B', 601, 190
union
select 7, 'Keith', '1986-09-05 00:00:00.000', 'M','C', 600, NULL
HI Friends,
I have posted the DDL and DML. It's just an sample.
I want to update the fields that with same Name and DOB. It's like duplicate row but with primary key different.
How to do this ?
Any suggestions would be appreciated. Please let me know if I am not clear.
April 11, 2014 at 1:04 am
Window function solution, works on 2012 and later
😎
Create Table #Temp (ID int not null primary Key, Name varchar(25) not null, DOB datetime not null, Sex char(1), Race char(1), Height int, Weight int);
insert #Temp
select 1, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160 union
select 2, 'Kenneth', '1963-02-26 00:00:00.000', NULL,NULL, NULL, NULL union
select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223 union
select 4, 'Ryan', '1980-07-05 00:00:00.000', 'M','W', 507, 145 union
select 5, 'Robert', '1988-09-14 00:00:00.000', NULL,'B', 601, NULL union
select 6, 'Robert', '1988-09-14 00:00:00.000', 'M',NULL, NULL, 190 union
select 7, 'Keith', '1986-09-05 00:00:00.000', 'M','C', 600, NULL
;WITH UPD_TBL
AS
(
select
ID
,Name
,DOB
,FIRST_VALUE(Sex) OVER
(
PARTITION BY Name,DOB
ORDER BY Sex DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS Sex
,FIRST_VALUE(Race) OVER
(
PARTITION BY Name,DOB
ORDER BY Race DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS Race
,FIRST_VALUE(Height) OVER
(
PARTITION BY Name,DOB
ORDER BY Height DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS Height
,FIRST_VALUE(Weight) OVER
(
PARTITION BY Name,DOB
ORDER BY Weight DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS Weight
from #Temp
)
UPDATE T
SET
Sex = UT.Sex
,Race = UT.Race
,Height = UT.Height
,Weight = UT.Weight
FROM UPD_TBL UT
INNER JOIN #TEMP T
ON UT.ID = T.ID;
SELECT * FROM #Temp;
DROP TABLE #Temp;
Output
ID Name DOB Sex Race Height Weight
--- -------- ----------------------- ---- ---- ------- -------
1 Kenneth 1963-02-26 00:00:00.000 M C 516 160
2 Kenneth 1963-02-26 00:00:00.000 M C 516 160
3 William 1962-06-28 00:00:00.000 M C 600 223
4 Ryan 1980-07-05 00:00:00.000 M W 507 145
5 Robert 1988-09-14 00:00:00.000 M B 601 190
6 Robert 1988-09-14 00:00:00.000 M B 601 190
7 Keith 1986-09-05 00:00:00.000 M C 600 NULL
April 11, 2014 at 1:21 am
Though following code would work but I feel there must be a easy way around.. SSC experts might help us here
;with cte as
(
SELECT DISTINCT T1.NAME,T1.DOB,
CASE
WHEN T1.SEX IS NULL THEN T2.Sex
ELSE T1.Sex
END AS SEX,
CASE
WHEN T1.Race IS NULL THEN T2.Race
ELSE T1.Race
END AS RACE,
CASE
WHEN T1.Height IS NULL THEN T2.Height
ELSE T1.Height
END AS Height,
CASE
WHEN T1.Weight IS NULL THEN T2.Weight
ELSE T1.Weight
END AS Weight
FROM #Temp T1
FULL OUTER JOIN #Temp T2
ON T1.DOB = T2.DOB AND T1.Name = T2.Name
),
CTE1 AS
(
SELECT * FROM cte WHERE SEX IS NOT NULL AND RACE IS NOT NULL AND Height IS NOT NULL AND Weight IS NOT NULL
)
UPDATE T1
SET Sex = C.SEX,Race=C.RACE,Height=C.HEIGHT,Weight=C.WEIGHT
FROM #Temp T1
INNER JOIN CTE1 C
ON T1.Name = C.Name AND T1.DOB = C.DOB
April 11, 2014 at 1:45 am
Old-fashioned solution, works on every version of SQL Server:
-- Check using SELECT
SELECT *
FROM #Temp t1
INNER JOIN (
SELECT Name, DOB, Sex = MAX(Sex), Race = MAX(Race), Height = MAX(Height), Weight = MAX(Weight)
FROM #Temp
GROUP BY Name, DOB
HAVING COUNT(*) > 1
) t2 ON t2.Name = t1.Name AND t2.DOB = t1.DOB
-- Perform UPDATE
UPDATE t1 SET Sex = t2.Sex, Race = t2.Race, Height = t2.Height, Weight = t2.Weight
FROM #Temp t1
INNER JOIN (
SELECT Name, DOB, Sex = MAX(Sex), Race = MAX(Race), Height = MAX(Height), Weight = MAX(Weight)
FROM #Temp
GROUP BY Name, DOB
HAVING COUNT(*) > 1
) t2 ON t2.Name = t1.Name AND t2.DOB = t1.DOB
-- Check results
SELECT * FROM #Temp
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 11, 2014 at 2:33 am
Hi Chris,
The query you have given is really easy to understand....Thank you so much..
April 11, 2014 at 2:36 am
Hi Eirik,
First of all thanks to you because you have taught me new functions like unbounded. and a new way of writing query.....I have never heard about that....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply