Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    48

    Unanswered: insert from old table not working

    insert into table_history (
    select * from old table (
    delete from table_main where carton_no= '100'
    )
    )

    gives error SQL20165N. Both tables have same structure. I am trying move data from table_main to table_history

    insert into table_history
    select * table_main where carton_no= '100';

    delete from table_main where carton_no= '100';
    works fine

    Any suggestions . Ver DB2 v9.5

    Thanks in advance

  2. #2
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by phil72 View Post
    insert into table_history (
    select * from old table (
    delete from table_main where carton_no= '100'
    )
    )

    gives error SQL20165N. Both tables have same structure. I am trying move data from table_main to table_history

    insert into table_history
    select * table_main where carton_no= '100';

    delete from table_main where carton_no= '100';
    works fine

    Any suggestions . Ver DB2 v9.5

    Thanks in advance

    do it in two different queries:
    Insert into table_history select * table_main where carton_no= '100';

    delete from table_main where carton_no= '100';

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try:

    Code:
    WITH
     delete_op AS (
    SELECT *
      FROM OLD TABLE
           (DELETE FROM table_main
             WHERE carton_no = '100'
           )
    )
    SELECT COUNT(*) insert_count
      FROM FINAL TABLE
           (INSERT INTO table_history
            SELECT * FROM delete_op
           )
    ;

  4. #4
    Join Date
    Nov 2008
    Posts
    48
    Thanks
    tonkuma
    this code works fine in control center but i get SQLSTATE: 42601 when i try to deploy in procedure . i tried to assign count to varriable also

    set temp = (WITH
    delete_op AS (
    SELECT *
    FROM OLD TABLE
    (DELETE FROM table_main
    WHERE carton_no = '100'
    )
    )
    SELECT COUNT(*) insert_count
    FROM FINAL TABLE
    (INSERT INTO table_history
    SELECT * FROM delete_op
    )
    );

Posting Permissions

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