T-SQL syntax to add a unique value to field

I have a table with an empty field.  I would like to somehow count records that meet a criteria and assign a numeric value to that field.

My end result is to take info from a SQL table and dump it into a flat text file.  For each record that meets a criteria (BATCH = '999' and SEQUENCE is NULL) I want to put a unique number (1,2,3,4...) in the SEQUENCE field.  What I am running into is that I am not able to get SQL to look at each record individually.  It performs the update to all of the records in the SEQUENCE field and I am therefore not left with unique values.  Any help you can give would be greatly appreciated.

 

DATE       Amount     Branch  Batch   Sequence Account

---------- ---------- ------- ------- -------- -------

06/26/2002 117.08     33 658      999 NULL     105288

06/26/2002 117.00     33 658      999 NULL     301654

06/26/2002 80.00      33 658      999 NULL     032655

06/26/2002 255.00     33 658      999 NULL     311640

06/26/2002 200.00     33 658      999 NULL     145930

06/26/2002 70.00      33 658      999 NULL     126073

06/26/2002 70.00      33 658      999 NULL     126073
freydaddy1Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Try this loop:

set nocount on
declare @c int
set @c = 1
nextrecord:
SET ROWCOUNT 1
UPDATE report set seq = @c WHERE batch = 999 and seq IS NULL
IF @@ROWCOUNT >0  
BEGIN
  set @c = @c+ 1
  GOTO nextrecord
END
SET ROWCOUNT 0

select * from report where batch = 999 order by seq


CHeers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.