SQL Server 2012: update missing data in one table from another table

Hello -
I have a table with some missing values that can be updated based on values in other rows in the table.  

Take this data for example:
-----------------------------------------------------------------------------
Person |    Date      |  Place | Field1 | Field2 | Field3
-----------------------------------------------------------------------------
1            | 1/1/2001 |      A     |     1      |     2     |    3
1            | 1/1/2001 |      A     |             |            |    
1            | 1/1/2001 |      A     |             |            |    
2            | 2/2/2002 |      B     |     1      |     2    |    3
3            | 3/3/2003 |      C     |             |           |    
3            | 3/3/2003 |      C     |     1      |    2     |  3  
1            | 3/3/2003 |      C     |     1      |    2     |  3  

The Person, Date, and Place fields represent a "group" of records.
For each group, I need to update the missing values in Field1, Field2, and Field 3 with the values in the non-missing rows for that group.  

The desired result would look somthing like this:
-----------------------------------------------------------------------------
Person |    Date      |  Place | Field1 | Field2 | Field3
-----------------------------------------------------------------------------
1            | 1/1/2001 |      A     |     1      |     2     |  3
1            | 1/1/2001 |      A     |     1      |     2     |  3
1            | 1/1/2001 |      A     |     1      |    2      |  3
2            | 2/2/2002 |      B     |     1      |     2    |   3
3            | 3/3/2003 |      C     |     1      |    2     |   3
3            | 3/3/2003 |      C     |     1      |    2     |   3  
1            | 3/3/2003 |      C     |     1      |    2     |   3  

I have been playing with CTE's for this but I'm not confident "WITH" that.  :)  
Here's an example of what I'm trying to use identify the target rows (the rows that need to be updated):

;WITH PersonEventGroups_CTE AS
(SELECT *, ROW_NUMBER() OVER 
(PARTITION BY Person, Date, Place ORDER BY Person, Date, Place) AS RowNumber
FROM MyTable
WHERE Person IS NOT NULL)
SELECT RowNumber, * FROM PersonEventGroups_CTE 
INTO #Targets
WHERE RowNumber>'1'

Open in new window


Once I have the target rows, my plan was to update those joining on Person+Date+Place.  Any thoughts or ideas on this approach?

TIA!
LVL 1
ttist25Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
no need for row_number,  MAX will be sufficient , or MIN, or even AVG if all of the field1,2,3 values are numeric

this should work in MSSQL or Oracle

  SELECT person,
         date,
         place,
         MAX(field1) OVER (PARTITION BY person, date, place) field1,
         MAX(field2) OVER (PARTITION BY person, date, place) field2,
         MAX(field3) OVER (PARTITION BY person, date, place) field3
    FROM mytable
ORDER BY date, person DESC

Here's a the same example with more data variation to help demonstrate it works as requested

WITH
    mytable(person,
            date,
            place,
            field1,
            field2,
            field3)
    AS
        (SELECT 1, '1/1/2001', 'A', 1, 2, 3
         UNION ALL
         SELECT 1,
                '1/1/2001',
                'A',
                NULL,
                NULL,
                NULL
         UNION ALL
         SELECT 1,
                '1/1/2001',
                'A',
                NULL,
                NULL,
                NULL
         UNION ALL
         SELECT 2, '2/2/2002', 'B', 11, 22, 33
         UNION ALL
         SELECT 3,
                '3/3/2003',
                'C',
                NULL,
                NULL,
                NULL
         UNION ALL
         SELECT 3, '3/3/2003', 'C', 111, 222, 333 
         UNION ALL
         SELECT 1, '3/3/2003', 'C', 1111, 2222, 3333 )
 SELECT person,
         date,
         place,
         MAX(field1) OVER (PARTITION BY person, date, place) field1,
         MAX(field2) OVER (PARTITION BY person, date, place) field2,
         MAX(field3) OVER (PARTITION BY person, date, place) field3
    FROM mytable
ORDER BY date, person DESC


    PERSON DATE     PLACE     FIELD1     FIELD2     FIELD3
---------- -------- ----- ---------- ---------- ----------
         1 1/1/2001 A              1          2          3
         1 1/1/2001 A              1          2          3
         1 1/1/2001 A              1          2          3
         2 2/2/2002 B             11         22         33
         3 3/3/2003 C            111        222        333
         3 3/3/2003 C            111        222        333
         1 3/3/2003 C           1111       2222       3333

 

Open in new window


Nice thing about this method is it doesn't require a join or subquery back to the source table -so less io and should scale nicely as data volume increases.  Of course, it's possible to solve the problem by doing all the extra io, but why would you want to?  :)
1
 
Ryan ChongCommented:
are the values of Field1, Field2 and Field3 always are 1s, 2s and 3s respectively?

are you using MS SQL or Oracle in your case?
1
 
ttist25Author Commented:
Hi Ryan,

Thanks for the response.  The values of Field1, Field2, and Field3 are not always 1, 2, and 3.  The values will be the same within groups but not necessarily across groups.

I'm using MS SQL.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
>>Once I have the target rows, my plan was to update those joining on Person+Date+Place.  Any thoughts or ideas on this approach?
@Author - There are many options to handle this case.  You can use any of the below.

NOTE - UPDATE command is available for you at the end with tested output.

OPTION 1
--
SELECT   a.person,
         a.date,
         a.place,      
		 r1.f1 field1,
		 r1.f2 field2,
		 r1.f3 field3
FROM GroupOfRecords a
CROSS APPLY
(
	SELECT MAX(field1) f1,MAX(field2) f2,MAX(field3) f3
	FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place
)r1

Open in new window



OPTION 2
--
SELECT   a.person,
         a.date,
         a.place,
         (SELECT TOP 1 field1 FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place ORDER BY field1 DESC ) field1,
		 (SELECT TOP 1 field2 FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place ORDER BY field2 DESC ) field2,
		 (SELECT TOP 1 field3 FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place ORDER BY field3 DESC ) field3
FROM GroupOfRecords a

Open in new window



OPTION 3
--
SELECT   a.person,
         a.date,
         a.place,      
		 r1.field1,
		 r2.field2,
		 r3.field3
FROM GroupOfRecords a
CROSS APPLY
(
	SELECT TOP 1 field1 FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place ORDER BY field1 DESC
)r1
CROSS APPLY
(
	SELECT TOP 1 field2 FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place ORDER BY field2 DESC 
)r2
CROSS APPLY
(
	SELECT TOP 1 field3 FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place ORDER BY field3 DESC
)r3

Open in new window




OPTION 4
--
SELECT PERSON,DATE,PLACE
,ISNULL(MAX(F1) OVER(PARTITION BY person, date, place),field1) AS field1
,ISNULL(MAX(F2) OVER(PARTITION BY person, date, place),field2) AS field2
,ISNULL(MAX(F3) OVER(PARTITION BY person, date, place),field3) AS field3
FROM GroupOfRecords
CROSS APPLY ( VALUES( CASE WHEN field1 IS NOT NULL THEN field1 END )) AS A(F1)
CROSS APPLY ( VALUES( CASE WHEN field2 IS NOT NULL THEN field2 END )) AS B(F2)
CROSS APPLY ( VALUES( CASE WHEN field3 IS NOT NULL THEN field3 END )) AS C(F3)

Open in new window


OUTPUT

/*------------------------
OUTPUT 
------------------------*/
person      date     place field1      field2      field3
----------- -------- ----- ----------- ----------- -----------
1           1/1/2001 A     1           2           3
1           1/1/2001 A     1           2           3
1           1/1/2001 A     1           2           3
2           2/2/2002 B     11          22          33
3           3/3/2003 C     111         222         333
3           3/3/2003 C     111         222         333
1           3/3/2003 C     1111        2222        3333

(7 row(s) affected)

Open in new window



UPDATE COMMAND for you

--
UPDATE x
SET x.field1 = u.field1 , x.field2 = u.field2 , x.field3 = u.field3
FROM GroupOfRecords x
INNER JOIN 
(
	SELECT   a.person,
			 a.date,
			 a.place,      
			 r1.f1 field1,
			 r1.f2 field2,
			 r1.f3 field3
	FROM GroupOfRecords a
	CROSS APPLY
	(
		SELECT MAX(field1) f1,MAX(field2) f2,MAX(field3) f3
		FROM GroupOfRecords b WHERE a.person = b.person AND a.date = b.date AND a.place = b.place
	)r1
)u ON u.date = x.date AND u.person = x.person AND u.place = x.place
--

Open in new window


DATA AFTER UPDATE

/*------------------------
SELECT * FROM GroupOfRecords x
------------------------*/
person      date     place field1      field2      field3
----------- -------- ----- ----------- ----------- -----------
1           1/1/2001 A     1           2           3
1           1/1/2001 A     1           2           3
1           1/1/2001 A     1           2           3
2           2/2/2002 B     11          22          33
3           3/3/2003 C     111         222         333
3           3/3/2003 C     111         222         333
1           3/3/2003 C     1111        2222        3333

(7 row(s) affected)

Open in new window


Please let us know in case of any issues.
0
 
ttist25Author Commented:
Thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.