How to duplicate rows via pl/sql?-CodePudding

what is the best way to copy rows from a table and insert into the same table with some value changed. say i have a table like below. I want to select all the rows with year 2021 and insert into the same table and just switch the year to 2022. need to do this with pl/sql.


id | projects   | year | employeeID

1  | chandra    | 2019 | 7

2  | alphaone   | 2020 | 2

3  | madinc     | 2021 | 3

4  | runa       | 2021 | 1

CodePudding user response：

INSERT can be combined with a SELECT query.

For example:

insert into t (id, projects, year)
select id, projects, 2022
from t
where year = 2021

CodePudding user response：

I think that id is a serial

so a simple INSERT INTO SELECT is enough

The econf DISTINCT subselect is not necessary if the project exist onl yonce per year

CREATE TABLE table1 (
  "id" SERIAL,
  "projects" VARCHAR(8),
  "year" INTEGER,
  "employeeID" INTEGER
);

INSERT INTO table1
  ("id", "projects", "year","employeeID")
VALUES
  ('1', 'chandra', '2019',7),
  ('2', 'alphaone', '2020',4),
  ('3', 'madinc', '2021',2),
  ('4', 'runa', '2021',1);

✓

4 rows affected

INSERT INTO table1 ("projects", "year","employeeID")  SELECT "projects",'2022',"employeeID" FROM (SELECT DISTINCT "projects","employeeID" FROM table1 WHERE "year" = 2021) t1

2 rows affected

SELECt * FROM table1

id | projects | year | employeeID
-: | :------- | ---: | ---------:
 1 | chandra  | 2019 |          7
 2 | alphaone | 2020 |          4
 3 | madinc   | 2021 |          2
 4 | runa     | 2021 |          1
 1 | madinc   | 2022 |          2
 2 | runa     | 2022 |          1

db<>fiddle here