如何在复制部分列的同时更改一列？

Question

我想复制第三列和第四列，但更改第二列。我该怎么办？我想知道如何编写sql查询。谢谢。

例如：

table1：

1, 1, aaa, bbb

2, 1, ads, bff

3, 1, awq, bcc

我希望table1最终成为：

1, 1, aaa, bbb

2, 1, ads, bff

3, 1, awq, bcc

4, 2, aaa, bbb

5, 2, ads, bff

6, 2, awq, bcc

(第一列是id)

Answer 1

这应该是可行的：

insert into table1 (column2,column3,column4) 
  select 2,column3, column4 
  from table1 where column2 = 1  

Answer 2

按照您的建议：

select (@cnt:=@cnt + 1) as RowNumber,a.c1,a.c2,a.c3 from
(select 1 as id, 1 as c1, 'aaa' as c2, 'bbb'as c3 union all
select 2, 1, 'ads', 'bff' union all 
select 3, 1, 'awq', 'bcc'

union all

select 1 as id, 1 as c1, 'aaa' as c2, 'bbb'as c3 union all
select 2, 1, 'ads', 'bff' union all 
select 3, 1, 'awq', 'bcc') as a

cross JOIN 

(select @cnt:=0) as tmp

结果：

1   1   aaa bbb
2   1   ads bff
3   1   awq bcc
4   1   aaa bbb
5   1   ads bff
6   1   awq bcc