Copy rows into the same table with new values

jaqaz · #1 (**permalink**) 08-10-11, 05:42

Current
TABLE 1
Col1 Col2 Col3 Col4 Col5 Col6
657 BB 0 10/08/2011 09:05 User
658 BB 1 10/08/2011 09:05 User
659 BB 2 10/08/2011 09:06 User
660 BB 3 10/08/2011 09:06 User
661 BB 8 10/08/2011 09:06 User
662 BB 9 10/08/2011 09:06 User
663 BB 10 10/08/2011 09:06 User
664 BB 11 10/08/2011 09:06 User

I want to copy the above rows into the same table with replaced col2 values as CC. Also note that Col1 is autonumber so i would like the new set of rows autonumbered.

DESIRED RESULT
TABLE

Col1 Col2 Col3 Col4 Col5 Col6
657 BB 0 10/08/2011 09:05 User
658 BB 1 10/08/2011 09:05 User
659 BB 2 10/08/2011 09:06 User
660 BB 3 10/08/2011 09:06 User
661 BB 8 10/08/2011 09:06 User
662 BB 9 10/08/2011 09:06 User
663 BB 10 10/08/2011 09:06 User
664 BB 11 10/08/2011 09:06 User
num CC 0 10/08/2011 09:05 User
num CC 1 10/08/2011 09:05 User
num CC 2 10/08/2011 09:06 User
num CC 3 10/08/2011 09:06 User
num CC 8 10/08/2011 09:06 User
num CC 9 10/08/2011 09:06 User
num CC 10 10/08/2011 09:06 User
num CC 11 10/08/2011 09:06 User

Thanks

r937 · #2 (**permalink**) 08-10-11, 06:11

Code:

INSERT
  INTO table1
     ( Col2
     , Col3
     , Col4
     , Col5
     , Col6 )
SELECT 'CC'
     , Col3
     , Col4
     , Col5
     , Col6
  FROM table1
 WHERE Col1 BETWEEN 657 AND 664

Internet Services

Web Development

Digital Marketing

Consumer Tech