We help IT Professionals succeed at work.

update with level break number

Rozamunda
Rozamunda asked
on
Hello, how would you do it in SQL

here is my table


 Prod ID,   seq  ,   action
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          1          'CREATE'
  art1          1          'REJECT'
  art1          1          'CLOSE'
 
 
 I know that first action is always  'CREATE' for any PRod ID, now I want to correct sequence number for products which occur more than once,  so I would want to have something like this:

 
  Prod ID,   seq  ,   action
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          2          'CREATE'
  art1          2          'APPROVE'
  art1          2          'CLOSE'
 
  art1          3          'CREATE'
  art1          3          'REJECT'
  art1          3          'CLOSE'
Comment
Watch Question

you want something like

select prodId, row_number() over(partition by prodId, action order by prodId,action) seq, action
from your_table

Author

Commented:
thanks, but how to update all the rows ?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Rozamunda,

Momi's query will properly generate a new SEQ number for all of the rows, but in terms of your data it may not be that simple.

Using just two items from your example:

Prod ID,   seq  ,   action
  art1          1          'CREATE'
  art1          1          'CREATE'
  art1          1          'APPROVE'
  art1          1          'CLOSE'
 
  art1          1          'APPROVE'
  art1          1          'CLOSE'

If the CREATE actions are resequenced, what protocol should be used to assign the correct row with an APPROVE or CLOSE action to the correct CREATE action?  Is there a timestamp or other item in the row that can help to better identify the rows?


Kent

Author

Commented:
Hi Kent, action is never resequenced, CREATE is always starting the section, CLOSE finishing,
there is also a time stamp (date, time)
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hmm....  I'm not sure that my comment was clear, so...

Can it ever happen that for any product ID, you have two OPEN at the same time?  That is, an OPEN occurs for a product, and before that event has a matching CLOSE, another OPEN is issued?

Kent

Author

Commented:
Kent, ultimately not,  one pair (productid/seqno) should have one open and one close, all in sequence. Right now everything has only one sequence number i want to fix the data, so the above would take place
if you run my query (And adding the timestamp column to the order by part)
do you get the desired results?
if so, we will turn that into an update statement
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Based on what you've said, I think that I'd follow Momi's advise and resequence the "seq" values for all of the OPEN statements, then follow that up with an UPDATE of the other statements to match everything up.


Kent

Explore More ContentExplore courses, solutions, and other research materials related to this topic.