October 21, 2011 at 8:59 am
Using the example data, I want to be able to update rows where the product and product line match but the product group does not and the product type may not either. Basically I want to consolidate duplicate rows where one has data (product group and product type) with a row that does not.
So for example the resulting test data would show one row for product 00001 that had a product group of 001 and a product type of 100.
create table #Products
(
product_line nchar(1),
product_group nchar(3),
product_type nchar(3),
product nchar(5)
)
insert into #Products
select '1','','','00001'
union all
select '1','001','100','00001'
union all
select '2','012','112','00001'
union all
select '3','','','00003'
union all
select '3','003','300','00003'
select *
from #Products
drop table #Products
October 21, 2011 at 11:08 am
Is the precendence such that the one you want to replace will always have a zero-length string as the value, and there will always be at most one other row with a valid value that can be put in there?
Or is it possible for there to be multiple rows with different values?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 21, 2011 at 11:17 am
And will you always have two empty strings as the duplicate? If not, how do you decide what values are correct?
October 21, 2011 at 11:31 am
GSquared (10/21/2011)
Is the precendence such that the one you want to replace will always have a zero-length string as the value, and there will always be at most one other row with a valid value that can be put in there?Or is it possible for there to be multiple rows with different values?
Yes, it seems that there will be rows that have empty strings and one other that will not. Having said that, it may be possible for a row to have an empty product group but not an empty product type. However in these cases both values should be overwritten.
October 21, 2011 at 11:33 am
Is there any reason to not just delete the row with incomplete data? If you don't, you'll end up with two identical rows, and that's either a violation of basic Relational Data Theory, or "not how we roll", depending on how formal you want to be.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2011 at 3:36 am
GSquared (10/21/2011)
Is there any reason to not just delete the row with incomplete data? If you don't, you'll end up with two identical rows, and that's either a violation of basic Relational Data Theory, or "not how we roll", depending on how formal you want to be.
No I guess not and having thought more about the problem I'm going to try and address the problem at source rather than having to correct it retrospectively.
October 24, 2011 at 3:54 pm
For more complex type record matching problems, I turn to the ranking functions.
Example:
select * from
(
select *,
rank() over
(
partition by product_line, product
order by product_group desc
) dup_rank
from #Products
) x where dup_rank > 1;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 25, 2011 at 12:49 am
Thanks for the code snippet Eric.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply