Question 21740 in Mysql Question posted in
The official MySQL documentation can be found here.

How to copy paste a part of SQL table in the same table with updated column value – Mysql

i have one table TABLE 1

ID CLASS GENDER SUBJECT
A1 6A A1 ENG
A1 6A A1 MATH
A1 4A A1 COMPUTER
A1 3A A1 GK

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

Result should be something like this

ID CLASS GENDER SUBJECT
A1 6A A1 ENG
A1 6A A1 MATH
A1 4A A1 COMPUTER
A1 3A A1 GK
A2 6A A1 ENG
A2 6A A1 MATH
A2 4A A1 COMPUTER
A2 3A A1 GK

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

ID CLASS GENDER SUBJECT
A1 6A A1 ENG
A1 6A A1 MATH
A1 4A A1 COMPUTER
A1 3A A1 GK

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

ID CLASS GENDER SUBJECT
A2 6A A1 ENG
A2 6A A1 MATH
A2 4A A1 COMPUTER
A2 3A A1 GK

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

Tags:

2

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search