Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Incremental identifier except where already exists

    I have a table called JOB in which I need to update to make a little more specific. The Table itself consists of these (relevant) fields, and a little example data;

    ORG_UNIT, JOB_TITLE, JOB_NUMBER
    ADXX, ATTACHMENT, 000027
    ADPR, CONTRACT, 002439
    ADPR, ATTACHMENT, 012033

    For the purpose of this i'm only looking at OR_UNIT = 'ADXX'

    what I'm trying to do is rename the job number to something more specific and I need to relate it to the Org_Unit so therefore ORG_UNIT+JOB_NUMBER. This is the easy bit.

    What I actually want to do though is create an incremental 3 digits number that precede the 6 digit job number and relate to unique job titles so it will eventually look like ADXX-001-000027

    The problem I have is that if ORG_UNIT + JOB_TITLE i.e. "ADXX-ATTACHMENT" appears more than once then I want to relate the second/third/instance to this...

    ADXX, ATTACHMENT, 000027 will become ADXX-001-000027
    ADXX, CONTRACT, 020233 will become ADXX-002-020233
    ADXX, ATTACHMENT, 048322 will become ADXX-001-048322

    Can someone help?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by christyxo View Post
    Can someone help?
    a good php programmer

    this is not something you want to do with sql

    in fact, it's probably something you shouldn't be doing at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I'm confused as to why you think it's not something that should be done within SQL. It's a non-indexed field that has no link to any other table and is merely a reference field which needs to be updated in order to optimize the cross over with over systems (Not linked).

    The Update query is just for this sort of thing is it not?

    This is the base of the update query that we have in place;

    UPDATE JOB
    SET JOB_NUMBER = ORG_UNIT + " - " + " ??? - " + RIGHT(10000000 + JOB_NUMBER,6)
    WHERE ORG_UNIT = 'ADXX'
    AND ISNUMERIC(JOB_NUMBER) = 1

Posting Permissions

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