How to clone details of one user to another | Oracle |

Question

I have been trying to implement a code which should clone details of one user to another.

Table : Employee

Below is the data in employee table :

Name | Age | Access | URL 
Raj  | 22  | Y      | google
Raj  | 22  | Y      | Youtube 
Raj  | 22  | N      | Udemy 
Bob  | 24  | Y      | Spotify 

Expected output : Cloned the user Raj to Bob all his access

Name | Age | Access | URL 
Raj  | 22  | Y      | google
Raj  | 22  | Y      | Youtube 
Raj  | 22  | N      | Udemy 
Bob  | 24  | Y      | Spotify 
Bob  | 24  | Y      | google
Bob  | 24  | Y      | Youtube 
Bob  | 24  | N      | Udemy

I can achieve it by using cursor , but the problem with the cursor is not good when their is large amount of data in table.

Any other approach through which I can get it done will be appreciated !!!

Answer 1

Insert + not exists might be one option:

insert into employee (name, age, access, url)
select 'Bob', b.age, b.access, b.url            --> new user
from employee b
where b.name = 'Raj'                            --> existing user
and not exists (select null
                from employee c
                where c.name = 'Bob'            --> new user
                  and c.age = b.age
                  and c.access = b.access
                  and c.url = b.url);

Parametrize it, if you want (i.e. pass old/new names as parameters).

---

BTW, access is a reserved word and can't be used as a column name:

SQL> create table test (access number);
create table test (access number)
                   *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL>