How to clone details of one user to another

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 !!!

CodePudding user response：

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>

CodePudding user response：

Use a MERGE statement:

MERGE INTO Employee dst
USING (
  SELECT 'Bob' AS name, 24 AS age, "ACCESS", url
  FROM   employee
  WHERE  name = 'Raj'
) src
ON (src.name = dst.name AND src.url = dst.url)
WHEN MATCHED THEN
  UPDATE SET "ACCESS" = src."ACCESS"
WHEN NOT MATCHED THEN
  INSERT (name, age, "ACCESS", url)
  VALUES (src.name, src.age, src."ACCESS", src.url);

db<>fiddle here