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):
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!
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'
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!
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys
are you using MS SQL or Oracle in your case?