i have one table TABLE 1

I want to copy and paste the records in the same table(table1) with ID value as A2

Result should be something like this

Can someone give the SQL syntax??

what i am doing now is

i made a new table(table2) and copy paste the record and used update() to update the id value A1 TO A2 and then again insert table 2 back into table 1

CREATE TABLE table 2 AS SELECT * FROM table 1;

TABLE 2

Then, Used Update table2 SET ID = 'A2' Where ID= 'A1';

Then, INSERT INTO table1 select * From table2;

But it is long process i want a shortcut of this and not create any extra table and all

just one syntax or max 2

because i want to reduce the time

CodePudding user response：

So you just want to duplicate the existing A1 rows with a new value for ID right?

INSERT INTO TABLE1 (
    ID
    ,CLASS
    ,GENDER
    ,SUBJECT
    )
SELECT 'A2'
    ,CLASS
    ,GENDER
    ,SUBJECT
FROM TABLE1
WHERE ID = 'A1'

CodePudding user response：

As @NicoHaase suggested you, to accomplish this you could use INSERT INTO ... SELECT

Something like...

INSERT INTO [Table1]
SELECT 'A2', [CLASS], [GENDER], [SUBJECT]
FROM [Table1]
WHERE [ID] = 'A1'