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';

Please try:

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
)
);