Create Stored Procedure help ...

Hello Every1,
I'm trying to create a stored procedure which will do the following.
- Look at the table to determine if a customer has a duplicate value in
a column.
- If Yes, then replace the duplicate with the highest # for that column
for that particular customer.
- Loop through to check & update all customers.
I was reading SQL Server Books, but couldn't find any help.
Any help or suggestions would be highly appreciated.
ThanksI don't understand what you want to do. Can you show share sample data
"Tony Schplik" wrote:

> Hello Every1,
> I'm trying to create a stored procedure which will do the following.
> - Look at the table to determine if a customer has a duplicate value in
> a column.
> - If Yes, then replace the duplicate with the highest # for that column
> for that particular customer.
> - Loop through to check & update all customers.
> I was reading SQL Server Books, but couldn't find any help.
> Any help or suggestions would be highly appreciated.
>
> Thanks
>|||Thanks Arun for your quick reply.
Here is the sample of the data.
CUST_ID SEQ_NUM
6000010135 2
6000010135 1
6000010135 1
6000010135 2
6000010135 5
6000020512 1
6000020512 1
6000020512 1
6000020512 4
6000020512 4
6000020512 6
Hope this will give you a better picture. As you can see from the
sample data that I have customer with same SEQ_NUM, which is causing
problem.
What I have to do is to replace the next same SEQ_NUM for the same
customer with the highest.
So for customer '6000010135' after the update in the table in the
SEQ_NUM column I should have the following values.
2
1
6
7
5
Thanks in advance for your help|||Does SEQ_NO have any implicit meaning in the data? Can we set any values to
this field as long as they are unique?
if so then it is very easy. Just create a temp table
(seq_no int identity(1,1), customerid int) and do this:
insert temp(customerid) select customerid from my_table order by customerid
One problem here is that seq_no field here will not reset to 1 when a new
customer id starts. So your seq_no will be ever increasing. May not be an
issue if your seq_no field does not have any contextual significance.
Anothe easy way is to use a cursor to go over
select customerid, seq_no from mytable order by customerid, seq_no
and iterate through the records. remember the last pair processed. If this
pair is same, update seq_no with max + 1
There should be a set based solution here too. But i have not figured it out
still.
"Tony Schplik" wrote:

> Thanks Arun for your quick reply.
> Here is the sample of the data.
> CUST_ID SEQ_NUM
> 6000010135 2
> 6000010135 1
> 6000010135 1
> 6000010135 2
> 6000010135 5
> 6000020512 1
> 6000020512 1
> 6000020512 1
> 6000020512 4
> 6000020512 4
> 6000020512 6
> Hope this will give you a better picture. As you can see from the
> sample data that I have customer with same SEQ_NUM, which is causing
> problem.
> What I have to do is to replace the next same SEQ_NUM for the same
> customer with the highest.
> So for customer '6000010135' after the update in the table in the
> SEQ_NUM column I should have the following values.
> 2
> 1
> 6
> 7
> 5
> Thanks in advance for your help
>