asked on

Update with row count based on a qualifyer

Let's say I have table with these values:

ORDRNMBR,LNITMSEQ,ITEMNMBR,QTY,FLAG
1000,0,WIDGET,12,1
1000,0,BOXCUTTER,24,1
1000,0,CAMERA,1,0
1000,0,LUNCHBOX,2,1

Flag=1 means add to order. When I am done processing these are rows I want to see
1000,1,WIDGET,12,1
1000,2,BOXCUTTER,24,1
1000,3,LUNCHBOX,2,1

The camera is left off the order and the LNITMSEQ column now has a row number indicating the line counter on the order for the items that are to be included on the order. How do I structure update statement to accomplish this by updating the value for LNITMSEQ?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

Thank you both for your input. Both of these get me going in the right direction. I am optimizing old code and this little snippet is part of a very large process. There are hundreds of thousands of records in this table with another flag field to indicate if the sales button was clicked. So the where clause starts with an order number and then looks at two flag fields. If the record qualifies it gets sent off to another table with the correct line number to be turned into an order to be processed in an ERP solution.