CODEWITHSUNDEEP
sql
Brad
Brad

Reputation: 12262

duplicate rows in table, but need to change data in one column

I have the table step_year: id, step_id, year

I want to duplicate all rows that have year = 1011 (select * from step_year where year = 1011), but instead of those newly created rows having the same year (1011) I want to change it to 1112.

Any help is appreciated.

Upvotes: 0

Views: 2414

Answers (4)

Jarry Bruckheimer
Jarry Bruckheimer

Reputation: 17

If id field is auto incremental, use this

INSERT INTO step_year(step_id,year)
           SELECT step_id, 1112 FROM step_year WHERE year = 1011

if id field is not auto incremental

INSERT INTO step_year(id, step_id, year)
           SELECT ROW_NUMBER() OVER(ORDER BY id) + COUNT(year) id, step_id, 1112 FROM step_year WHERE year = 1011

Upvotes: 1

Marlin Pierce
Marlin Pierce

Reputation: 10089

If your database supports it:

INSERT INTO step_year
(id, step_id, year)
SELECT sub_year.id, sub_year.step_id, 1112
  FROM step_year sub_year
  WHERE sub_year.year = 1011

Upvotes: 1

Greg Oks
Greg Oks

Reputation: 2730

insert into step_year(id, step_id,year)
select id,step_id,1112 from step_year where year = 1011

Upvotes: 1

doublesharp
doublesharp

Reputation: 27607

You can do the INSERT based on a SELECT but specify the year instead of pulling it from the query

INSERT INTO `step_year` (`id`, `step_id`, `year`)
SELECT `id`, `step_id`, 1112 AS `year` 
FROM `step_year` where `year` = 1011

This syntax is for MySQL, but should be adaptable to most databases.

Upvotes: 1

Related Questions