create a SQL (File-> New-> SQL) object with following SQL command

"insert into table1 (name, effstatus, age) select field1, 'I', field2 from table1 where ....."

use SQLEXEC to invoke the SQL object wherever required in pplcode.

1. Create a CI on the Position Data component.
2. Write a simple insert SQL by querying the POSTION_DATA table.... insert into ps_position_data select position_nbr, effdt,eff_status....from ps_position_data where ....

IF I WILL DO THE SAME HOW CAN I CHANGE MY EFF_STATUS AS INACTIVE?

HI gUYS I RESOLVED IT
THANKS FOR UR EARLY RESPONCE.

peoplekishore, can you share with us how you were able to accomplish that?

I CREATED AN SQL OBJECT AND USING GETSQL AND FETCH SQL I AM GETTING THE VALUES OF SELECTED FIELDS AND I CREATED ONE RECORD OBJECT AND I INSERTED A NEW ROW INTO IT WITH MODIFICATIONS.

I am pretty sure this can be acheived thru one single insert sql (set based).
The only problem is You cannot insert and select using same table.
It'll fail on DB2 and Informix.
so It'll be 2 step insertion.
Insert into some temp table and insert back using %insertselect and override the value of Eff Status.

Regards
Nitin