Looking for SQL UPDATE Query 1

[SMA00]

Hi Friends,

I have following query looking for solution.

Table AAA ; SalesmanID char (02)
Customer ID char (15)
NRec int
Srno int
Existing Data:
SalesmanID Customer ID NRec SRNO
90 ALKUP001 3 0
90 ALKHT712 3 0
90 NYKY8920 3 0

92 CHG8993 2 0
92 DLK8837 2 0


Looking for Result:

Data: SalesmanID Customer ID NRec SRNO
90 ALKUP001 3 1
90 ALKHT712 3 2
90 NYKY8920 3 3

92 CHG8993 2 1
92 DLK8837 2 2

Can some one provide the SQL query how to update srno field starting with 1 based on NREC field group by salesmanID.
Generating serial number by Salesman ID.

Thanks
SM

 

[vongrunt]

Do you want EXACTLY that ranking order? Or any order is good enough as long as (SalesmanID, Srno) are unique?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SMA00]

Hi Vongrunt,

Thanks for your promt reply. As long as Salesman and Srno are maintain unique any order will be fine.

Looking for you reply.

Best Regards,
SM

 

[vongrunt]

Ok then... here is one way:

-- update aaa set srno = 0
declare @srno int; set @srno = 1

declare @rc int; set @rc = 1
while @rc > 0
begin
	update T
	set srno = @srno
	from aaa T
	where CustomerID in 
	(	select min(CustomerID)
		from aaa
		where SalesmanID= T.SalesmanID and SrNo = 0
	)
	and SrNo = 0

	set @rc = @@rowcount
	set @srno = @srno + 1
end

select * from aaa

This code updates all rows with SRNO=1 first, then 2, then 3 etc.

Normally, you must have some sort of unique/primary key; in this case I assumed CustomerID is unique within a group (for each SalesmanID).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SMA00]

Hi Vongrunt,

Great Job Vongrunt. Query works fine as i tested with 50 records. I will update tomorrow one of my table having 40000 records.

Thanks Very much for your support.

Best Regards
SM