Link to home
Start Free TrialLog in
Avatar of ttist25
ttist25

asked on

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!
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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?
Avatar of ttist25
ttist25

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ttist25

ASKER

Thanks guys