#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Help with an update statement


    Hi

    Firstly, I can will need to run this statement in mysql or access db, either is fine by me as I can do it at source in the access db or in the mysql db I export to.

    Basically I want an update statement which adds a sequential number to a table for a select statement. I dont mind if every time I run it that it populates every row again. It does not need to find the last sequence number.

    Dont worry about any of the table names etc below being reserved words, ive just done as an example to try and keep as simple as possible.

    Ive made these names up so that it makes more sense.
    Table name is called TABLEA
    Within TABLEA are NAME, UNIQUENUMBER, DATE, SEQUENCE

    Basically my primary key so to speak is on NAME, UNIQUENUMBER

    What I want to do is write an update statement which populates the SEQUENCE column with a sequential number for the NAME but the sequence must then restart for the next NAME.

    I.E My select would be
    Select NAME, UNIQUENUMBER
    From TABLEA
    Order by NAME, UNIQUENUMBER desc

    Lets say my data from the select is
    • NAME UNQIENUMBER
      PETER 007
      PETER 004
      PETER 001
      JOHN 101
      JOHN 099
      GARY 078
      GARY 064
      GARY 045
      GARY 038


    I want the update statement to populate the SEQUENCE so that my data would now be

    • NAME UNIQUENUMBER SEQUENCE
      PETER 007 1
      PETER 004 2
      PETER 001 3
      JOHN 101 1
      JOHN 099 2
      GARY 078 1
      GARY 064 2
      GARY 045 3
      GARY 038 4


    I need to achieve this as I then need to run some scripts based on the SEQUENCE for each NAME.

    Thanks for your help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    In mysql you can use auto_increment in a compound key - in this case (name,id).

    Note that the table ENGINE needs to be MyISAM for this to work, but the manual covers all this.

    Alternatively, you could just calculate the value 'on-the-fly':
    Code:
    DROP TABLE IF EXISTS my_table;
    CREATE TABLE my_table 
    (NAME VARCHAR(12) NOT NULL,UNIQUENUMBER INT NOT NULL,PRIMARY KEY(name,uniquenumber));
    
    INSERT INTO my_table VALUES
    ('PETER',007),
    ('PETER', 004),
    ('PETER', 001),
    ('JOHN',101),
    ('JOHN', 099),
    ('GARY', 078),
    ('GARY', 064),
    ('GARY', 045),
    ('GARY', 038);
    
    SELECT x.name
         , LPAD(x.uniquenumber,3,0) uniquenumber
         , COUNT(*) rank 
      FROM my_table x 
      JOIN my_table y 
        ON y.name = x.name 
       AND y.uniquenumber >= x.uniquenumber 
     GROUP 
        BY x.name
         , uniquenumber 
     ORDER 
        BY name DESC
         , rank;
    +-------+--------------+------+
    | name  | uniquenumber | rank |
    +-------+--------------+------+
    | PETER | 007          |    1 |
    | PETER | 004          |    2 |
    | PETER | 001          |    3 |
    | JOHN  | 101          |    1 |
    | JOHN  | 099          |    2 |
    | GARY  | 078          |    1 |
    | GARY  | 064          |    2 |
    | GARY  | 045          |    3 |
    | GARY  | 038          |    4 |
    +-------+--------------+------+

IMN logo majestic logo threadwatch logo seochat tools logo