Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    2

    Wink Unanswered: Changing values in a record

    I have the problem of dynamically changing a field value in a row depending on the value of the same field in the previous row, assuming the table is sorted on that field.

    You may consider that field as a key of same sort as other values in the table do not help in selecting them uniquely.
    Ex.
    Before After
    130 -> 130
    130 -> 130A
    140 -> 140
    140 -> 140A

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is the order of rows with "ties" arbitrary, or is there some hidden criteria that allows you to order them too? This is important, because it significantly changes the nature of the problem.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is this a one-time data fix, or will this process need to be run regularly?

    I assume, too, that data values such as 130B, 130C, etc... may also be required?

    I have to say, it is rarely a good idea to create ID values this way (though I understand that sometimes the business model requires it), but it is also rare that a good application design requires stored data to be ordered in a specific manner, either logically or physically. Records should be independent from one-another.

    I suspect that this is a case of either:
    1) Poor application design.
    2) A data-import issue.
    3) A homework problem for a class.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    May 2004
    Posts
    2

    Cool SQL Brainteaser

    Yessss,
    It is a problem of moving data from a legacy system (a whole lot of rubbish) into a new ERP. Business logics and data models are way different. Under this circumstances, a non-key field in the old system is now a key in the new model, from here the need of updating the values with a trailer character (or what have you).

    Blindman, the values you mentioned are allowed, altough the repetition is often limited to two rows only.

    Pat, there are no possible sorting orders because the other fields may vary 'randomly'.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    For a one-time shot, and given data that has no natural order beyond the field you are dealing with, a cursor may be a valid option.

    Create a cursor that loops through the dataset order by your field, and that updates the field value if the prior field value had the same ID.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you don't have any other way to impose order on the rows, then a cursor is the only option that you've got left. Its messy and a "last resort", but it will get the job done!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •