OraFAQ Forum: SQL & PL/SQL » insert with different values

Home » SQL & PL/SQL » SQL & PL/SQL » insert with different values (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

insert with different values [message #614066]

Thu, 15 May 2014 07:06

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Dear Experts,

I have a table with lots of records with one field whose values will be changed and re-inserted back into the same table with other value but keeping the old records as well.

For example.




CREATE TABLE FM_CMP (AC_COMP_CODE VARCHAR2(12), AC_ACNT_CODE VARCHAR2(12), AC_CR_UID VARCHAR2(12), AC_CR_DT DATE)


INSERT INTO FM_CMP (AC_COMP_CODE , AC_ACNT_CODE , AC_CR_UID, AC_CR_DT) VALUES ('RK','11350001','TJ',SYSDATE);


INSERT INTO FM_CMP (AC_COMP_CODE , AC_ACNT_CODE , AC_CR_UID, AC_CR_DT) VALUES ('RK','11350002','TJ',SYSDATE);


INSERT INTO FM_CMP (AC_COMP_CODE , AC_ACNT_CODE , AC_CR_UID, AC_CR_DT) VALUES ('RK','11350003','TJ',SYSDATE);


AC_COMP_CODE	AC_ACNT_CODE	AC_CR_UID	AC_CR_DT
RK	11350001	TJ	5/15/2014 3:45:36 PM
RK	11350002	TJ	5/15/2014 3:46:21 PM
RK	11350003	TJ	5/15/2014 3:46:28 PM



-- i want to insert the same data but with different AC_COMP_CODE AS 'KJR' 


AC_COMP_CODE	AC_ACNT_CODE	AC_CR_UID	AC_CR_DT
RK	11350001	TJ	5/15/2014 3:45:36 PM
RK	11350002	TJ	5/15/2014 3:46:21 PM
RK	11350003	TJ	5/15/2014 3:46:28 PM
KJR	11350001	TJ	5/15/2014 3:45:36 PM
KJR	11350002	TJ	5/15/2014 3:46:21 PM
KJR	11350003	TJ	5/15/2014 3:46:28 PM

Report message to a moderator

Re: insert with different values [message #614067 is a reply to message #614066]

Thu, 15 May 2014 07:17

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> insert into FM_CMP (AC_COMP_CODE , AC_ACNT_CODE , AC_CR_UID, AC_CR_DT)
  2  select 'KJR', AC_ACNT_CODE , AC_CR_UID, AC_CR_DT from FM_CMP
  3  /

3 rows created.

SQL> select * from FM_CMP
  2  /
AC_COMP_CODE AC_ACNT_CODE AC_CR_UID    AC_CR_DT
------------ ------------ ------------ -------------------
RK           11350001     TJ           15/05/2014 14:16:00
RK           11350002     TJ           15/05/2014 14:16:00
RK           11350003     TJ           15/05/2014 14:16:01
KJR          11350001     TJ           15/05/2014 14:16:00
KJR          11350002     TJ           15/05/2014 14:16:00
KJR          11350003     TJ           15/05/2014 14:16:01

6 rows selected.

Report message to a moderator

Re: insert with different values [message #614068 is a reply to message #614066]

Thu, 15 May 2014 07:19

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

SQL> INSERT INTO FM_CMP
  2     SELECT 'KJR', AC_ACNT_CODE, AC_CR_UID, AC_CR_DT FROM FM_CMP;
3 rows inserted

SQL> select * from fm_cmp;
AC_COMP_CODE AC_ACNT_CODE AC_CR_UID    AC_CR_DT
------------ ------------ ------------ --------------------
RK           11350001     TJ           5/15/2014 5:16:32 AM
RK           11350002     TJ           5/15/2014 5:16:32 AM
RK           11350003     TJ           5/15/2014 5:16:32 AM
KJR          11350001     TJ           5/15/2014 5:16:32 AM
KJR          11350002     TJ           5/15/2014 5:16:32 AM
KJR          11350003     TJ           5/15/2014 5:16:32 AM
6 rows selected

Edit : Michel already posted by the time I clicked submit button Smile