Copy Data From One Column To Another in Oracle

Copy Data From One Column To Another in Oracle | In this post we will learn how to copy the data from one column to another in Oracle either in the same table or in a different table.

First, let’s create the employee table and insert some records into it.

CREATE TABLE employees
(
   person_id  NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name  VARCHAR2(50),
   sal        NUMBER
);

Let us insert some records to the employees by executing the following query.

INSERT INTO employees
VALUES (1, 'Plash', 'Roy', 99000);

INSERT INTO employees
VALUES (2, 'Mark', 'Hudson', 87000);

INSERT INTO employees
VALUES (3, 'Dallas', 'Christy', 95000); 

SELECT * FROM employees;

Copy Data From One Column To Another in Oracle in 2 Tables

Now let’s create another table called members.

CREATE TABLE members
(
   id  NUMBER PRIMARY KEY,
   name VARCHAR2(50),
   sub_name VARCHAR2(50),
   sal        NUMBER
); 

To copy the data from one column/more columns to another oracle, we will execute the below query:-

INSERT INTO members(id, name)
SELECT person_id, first_name
FROM employees; 

It will copy person_id and first_name from the employees’ table to the id and name column of the members’ table. Let’s check the output of the table members by executing the following query:-

SELECT * FROM members; 

Output:-

ID  NAME   SUB_NAME  SAL
--  ------ --------  ---
 1  Plash             
 2  Mark              
 3  Dallas            

From the above output, we can demonstrate how we copy column data from one table to another in oracle for 2 different tables.

Copy Data From One Column To Another in Oracle in the Same Table

Let’s write a query to copy data from one column to another in the same table. Syntax:-

UPDATE table_name
SET    destination_column_name = orig_column_name
WHERE  condition_if_necessary.

For example, we will write a query based on the above syntax:-

UPDATE members
SET sub_name = name; 

Let’s check the records in the table member by executing the query:-

SELECT * FROM members;

Output:-

      ID    NAME     SUB_NAME   SAL
---------- -------- ---------- -------
      1      Plash    Plash    
      2      Mark     Mark     
      3      Dallas   Dallas   

From the above output, we can say that we have copied the data of one column to another in the same table.

Now, let us write the query to copy data from one column to another in the two different tables. Let us execute the following query:-

UPDATE members
SET sal = 
(SELECT sal FROM employees
WHERE  members.id = employees.person_id); 

Lets us now check the table by executing the following query.

SELECT * FROM members; 

Output:-

      ID    NAME     SUB_NAME   SAL
---------- -------- ---------- -------
      1      Plash    Plash     99000
      2      Mark     Mark      87000
      3      Dallas   Dallas    95000

Also see:- Constraints in Oracle

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search