This forum is currently locked. You can't register or post questions at this time. (read more)

Insert from one column to another with in the same table...

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

mdsirajoddin
Member
Posts: 25
Joined: Mon Sep 09, 2013 4:30 am

Insert from one column to another with in the same table...

Postby mdsirajoddin » Mon Sep 09, 2013 4:52 am

Hi Tim,

Code: Select all

CREATE TABLE EMP (
 EMPNO               NUMBER(4) NOT NULL,
 ENAME               VARCHAR2(10),
 JOB                 VARCHAR2(9),
 MGR                 NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
 HIREDATE            DATE,
 SAL                 NUMBER(7,2),
 COMM                NUMBER(7,2),
 DEPTNO              NUMBER(2) NOT NULL,
 CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO));

INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);


this is the general emp table....

I added one more column like old_sal to take the backup of sal column data

Code: Select all

alter table emp add old_sal number(7,2);


Now what I want is, I want to move the data from "sal" to old_sal column on basis of empno i.e., primary key column..

Is it possible through directly from here... data insertion from one column to another column with in the same table..

or is it possible :
I created one more table with primary key column and required sal column:

Code: Select all

Create table emp_sal as select empno, sal from emp


I mean is it possible to insert the data into emp(old_sal) from emp_sal table on base of Primary Key..

pls let me know the way how i can insert all the values from one to another... with in the same table...


Thanks in Advance..

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Insert from one column to another with in the same table

Postby Tim... » Mon Sep 09, 2013 7:49 am

Hi.

OK. I see what you are asking now. You could so that with a scalar subquery, like this:

Code: Select all

UPDATE emp e1
SET    e1.old_sal = (SELECT e2.sal
                     FROM   emp_sal e2
                     WHERE  e1.empno = e2.empno);


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mdsirajoddin
Member
Posts: 25
Joined: Mon Sep 09, 2013 4:30 am

Re: Insert from one column to another with in the same table

Postby mdsirajoddin » Mon Sep 09, 2013 8:00 am

Tim... wrote:Hi.

OK. I see what you are asking now. You could so that with a scalar subquery, like this:

Code: Select all

UPDATE emp e1
SET    e1.old_sal = (SELECT e2.sal
                     FROM   emp_sal e2
                     WHERE  e1.empno = e2.empno);


Cheers

Tim...

Thanks a lot...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Insert from one column to another with in the same table

Postby Tim... » Mon Sep 09, 2013 8:20 am

No worries. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mdsirajoddin
Member
Posts: 25
Joined: Mon Sep 09, 2013 4:30 am

Re: Insert from one column to another with in the same table

Postby mdsirajoddin » Thu Sep 19, 2013 10:29 am

hi, Tim

is the same possible through insert statement...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Insert from one column to another with in the same table

Postby Tim... » Thu Sep 26, 2013 6:52 pm

Hi.

Yes. You can insert scalar subqueries into insert statements.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 5 guests