Hi,

I am trying to flag the Unique RowID in SQL Server 2000. Something like this :

OptyID ManagedBy UniqueIdentifier
2-A23RT A Flag
2-A23RT B NULL
2-A23RT C NULL
1-QRT87 D Flag
A-89712 E Flag
A-89712 F NULL

So, it will go through with every single row and flag only the first unique OptyID (say "Flag"). If the ID repeats then it will set a NULL value.

Can someone please advice, how can i do that.

Your help would be greatly appreciated.

Thanks in advance
R

Here you go

create table BlahTable (OptyID varchar(10), ManagedBy char(1), [UniqueIdentifier] varchar(4))
insert into BlahTable
select '2-A23RT','A',NULL union all
select '2-A23RT','B',NULL union all
select '2-A23RT','C',NULL union all
select '1-QRT87','D',NULL union all
select 'A-89712','E', NULL union all
select 'A-89712','F',NULL

update b
set [UniqueIdentifier] = 'FLAG'
From BlahTable b join(
select OptyID,Min(ManagedBy) ManagedBy from BlahTable
group by OptyID) z on b.OptyID = z.OptyID and z.ManagedBy =b.ManagedBy

select * from BlahTable


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/

I feel obligated to point out that SqlMenace's response "flag"s the row with the lowest (i.e. MIN) ManagedBy value for each OptyID.

The OP asked for the "first"; not necessarily the same thing.

On the other hand, the concept of "first" has no meaning in SQL. Since rows in a table are unordered, there can be no meaning to the concept of a "first" row, which implies an ordering of some sort.

Thus, SqlMenace's solution is as correct as it could be, given the flawed premise.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks a million. I very much appreciate it.

R

Firstly Thank heaps. this is my exact situation. Sometimes opty are managed by same person but different product attached to the opty and sometimes opty is managed by different people but it has got same product attached. Like the example below :

OptyID ManagedBy Product UniqueIdentifier
2-A23RT A AA Flag
2-A23RT B AA
2-A23RT C AA
1-QRT87 D WQ Flag
1-A89712 E TR Flag
1-A89713 E RV
1-A89714 E ZZ
8-A89714 A AA Flag

Your query works fine if i take product off the table. Any ideas how to fix it?

thanks for your help
R