Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2012
    Posts
    19

    Question Unanswered: How to define script to reorganize composite key

    I am stuck with a problem to reorganize my composite key.

    My table:

    --------------- old --------------------
    create table t (code char(20), lfd int);

    insert into t (lfd, code) values (1, 'Anton');
    insert into t (lfd, code) values (2, 'Anton');
    insert into t (lfd, code) values (3, 'Anton');
    insert into t (lfd, code) values (1, 'Bert');
    insert into t (lfd, code) values (2, 'Bert');
    insert into t (lfd, code) values (3, 'Bert');
    insert into t (lfd, code) values (1, 'ANTON');
    insert into t (lfd, code) values (2, 'ANTON');
    insert into t (lfd, code) values (1, 'CHARLIE');

    create unique index idx_t on t (code, lfd);

    ----------------------------------------
    Due to a program error Anton and ANTON were handled as different codes. This is to be simplified to the following table as followed:

    --------------- new --------------------
    create table t (code char(20), lfd int);

    insert into t (lfd, code) values (1, 'ANTON');
    insert into t (lfd, code) values (2, 'ANTON');
    insert into t (lfd, code) values (3, 'ANTON');
    insert into t (lfd, code) values (4, 'ANTON');
    insert into t (lfd, code) values (5, 'ANTON');
    insert into t (lfd, code) values (1, 'BERT');
    insert into t (lfd, code) values (2, 'BERT');
    insert into t (lfd, code) values (3, 'BERT');
    insert into t (lfd, code) values (1, 'CHARLIE');

    create unique index idx_t on t (code, lfd);

    ----------------------------------------

    My question: How can i transform the data?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    CREATE TABLE t2
    ( lfd INTEGER
    , code VARCHAR(99) 
    , PRIMARY KEY (lfd, code)
    );
    INSERT INTO t2
    SELECT DISTINCT lfd, UPPER(code)
    FROM t
    ;
    DROP TABLE t
    ;
    RENAME TABLE t2 TO t
    ;
    i don't think ANSI SQL has an UPPER function, and i'm not at all sure that ANSI SQL has a RENAME command

    however, whatever database system you're working with surely will
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    19
    @r937: this cant work. Please have a look at the column "lfd".

    I need ANTON with the lfd 1, 2, 3, 4 and 5.

    And yes, Ansi has a toupper() - function

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Use this
    Code:
    INSERT INTO t2 (lfd, code)
    SELECT row_number() over (partition by code order by lfd) as lfd,
           UPPER(code)
    FROM t;

  5. #5
    Join Date
    May 2012
    Posts
    19
    thanks @shammat, but row_number() is no ANSI.

    The statement i need has to work for Informix, Sybase and MySQL.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by koenigotto View Post
    I need ANTON with the lfd 1, 2, 3, 4 and 5.
    Code:
    INSERT INTO t2
    VALUES ( 4,'ANTON' ) , ( 5,'ANTON' )
    vwalah!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2012
    Posts
    19
    thanks @r937, but ANTON was only an example My table has a few thousand rows.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by koenigotto View Post
    thanks @shammat, but row_number() is no ANSI.
    It most definitely is (since 2003)

  9. #9
    Join Date
    May 2012
    Posts
    19
    i am sorry @shammat. You are right! Just Informix and MySQL doesnt follow the standard.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by koenigotto View Post
    Just Informix and MySQL doesnt follow the standard.
    i'm gonna guess they won't all use the TOUPPER function either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    The ANSI SQL core functionality includes UPPER. (TO_UPPER isn't ANSI SQL at all.)

    row_number() is included in the ANSI SQL standard as non-core functionality, feature T612, "Advanced OLAP operations".

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which kind of invites the question... what does koenigotto really want?

    1. ANSI SQL (likely no solution)
    2. compromise SQL that will work unchanged in all 3 databases
    3. different but optimum solution for each database

    if 3., the myisam engine can use auto_increment within code to simulate row_number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2012
    Posts
    19
    @937: at the time i asked the question, i expected to find an ANSI solution. Meanwhile i had to realize that such an answer cant be given...

    We have over 400 customers with different kind of databases: MySQL, Oracle, Informix and Sybase.

    I have to reorganize this damn table with ONE script. I can live with the fact to give the MySQL users an adopted script but not with the others.

    So, i really have to find ONE script for Oracle, Informix and Sybase.

  14. #14
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    My question: How can i transform the data?
    My question is: Do you want a script to go from your ---new--- to ---old--- examples, to undo the programming error?
    Or do you want a script to go from ---old--- to ---new---, which you said is the result of a programming error?


    update t set lfd = lfd + coalesce((select max(lfd) from t as ts
    where upper(ts.code) <> ts.code
    and upper(ts.code) = t.code),0)
    where upper(code) = code;
    update t set code = upper(code) where code <> upper(code);


    Core SQL -99.

  15. #15
    Join Date
    May 2012
    Posts
    19
    @JarlH, you are a genius! This indeed is an Ansi-solution. Nevertheless my f****** informix database doesnt understand it.

    I give up...

Posting Permissions

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