We help IT Professionals succeed at work.

Resequence the Serial Number column value

chooseroopesh
on
642 Views
Last Modified: 2010-05-18
I have a table Order_Detail table, in which I have Order_Id, Product_Id, and Serial_No columns.
My requirement is Some times when the rows get deleted, Serial No will not be in the order, so I have re sequence the Serial_No column.
for this I cant use a trigger (business requirement), need to re sequence only when user requests for it.
Please suggest me a solution which does not require a Cursor

Ex.
Order Id     Serial No    Product Id
---------------------------------------
1001            1               P001
1001            3               P002
1001            5               P003
1002            2               P006
1002            5               P010
1002            6               P020

Should become
Order Id     Serial No    Product Id
---------------------------------------
1001            1               P001
1001            2               P002
1001            3               P003
1002            1               P006
1002            2               P010
1002            3               P020

Help me please,
Many thanks in advance
Comment
Watch Question

Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
UPDATE yourtable t
  STE Serial_No = ( select count(*) from yourtable i where i.order_id = t.order_id and i.product_id <= t.product_id )
WHERE order_id = xxxx

Author

Commented:
Hi Aneesh,

Its just awesome,

~Roopesh
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.