Update values 1

[chamilz]

I have data set in my working table as below.

Code category

1500077 DEFLT
1500077 RELAX
------------------------------------
1500078 DEFLT
1500078 OREX
1500078 LATIN
------------------------------------
1500081 OCTAL
1500081 DEFLT

I need to update DEFLT category from the primary Category. Any category other than DEFLT is considered as primary category.

Result:

Code Category
1500077 RELAX ( DEFLT)
1500077 RELAX

1500081 OCTAL
1500081 OCTAL (DEFLT)



Exception: if there are more than 1 primary category then update NOT required.

1500078 DEFLT
1500078 OREX
1500078 LATIN


Greatly appreciate your help.

 

[vongrunt]

Maybe this (blah = your table, backup data first):

update A
set category = B.primaryCategory --  + ' (DEFLT)' :)
from blah A
inner join
(	select code, max(category) as primaryCategory
	from blah
	where category <> 'DEFLT'
	group by code
	having count(category) = 1
) B
on A.Code = B.Code
where A.category = 'DEFLT'

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[chamilz]

vongrunt:

I tested and it worked.

You deserve a start!

Thank for the help.