This will work, but I second the temp table idea..
select
distinct name, age, addr1, addr2 from
(
select
f1.name, f1.age,
case when f1.addr1 is null then f2.addr1 else f1.addr1 end as addr1,
case when f1.addr2 is null then f2.addr2 else f1.addr2 end as addr2
from
foouser f1
join foouser f2
on f1.name = f2.name
)
where
addr1 is not null
and
addr2 is not null
On Mon, Jan 24, 2011 at 1:18 PM, Dean Lawrence <dean...@gmail.com> wrote:
>
> Steve,
>
> Is the name ultimately supposed to be the unique key for this table?
> Instead of battling with these multiple records, you could create a
> temporary table, loop through all the records in your main table and
> either insert or update a record in the temp table based upon the
> name. Once you have done that, delete all the rows from your main
> table and then insert the merged records from your temp table back
> into the main table. You should only need to run this scrubbing
> procedure once.
>
> Dean
>
> On Mon, Jan 24, 2011 at 2:13 PM, DURETTE, STEVEN J (ATTASIAIT)
> <sd1...@att.com> wrote:
>>
>> That was just example data... The Name is actually a distinct number
>> letter combination. When the data is passed over to me they are supposed
>> to pre-merge the rows into one, but they don't so I have been tasked to
>> deal with it when the data suppliers don't. Another issue is that there
>> are MANY more columns, so I could actually have 20 rows with this
>> situation. They all merge into one though.
>>
>> Sorry for the confusion, I just redact true data whenever possible.
>>
>> -----Original Message-----
>> From: Greg Morphis [mailto:gmorp...@gmail.com]
>> Sent: Monday, January 24, 2011 2:00 PM
>> To: cf-talk
>> Subject: Re: Need a little sql help...
>>
>>
>> How do you know the 2 Steve's are the same Steve?
>> What if there was another Steve who was also 40 but lived elsewhere?
>> Can you provide some more realistic data? And how you'd know they were
>> the same Steve? or whomever?
>>
>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341150
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
