CODEWITHSUNDEEP
mysqlduplicates
Yanki Twizzy
Yanki Twizzy

Reputation: 8001

Duplicating SQL rows and renaming duplicated columns

I have a table that contains something like this ╔═══════╦══════════╗ ║ EMPID ║ UserName ║ ╠═══════╬══════════╣ ║ 1 ║ Josh ║ ║ 2 ║ Kate ║ ║ 3 ║ Josh ║ ║ 4 ║ Kate ║ ║ 5 ║ Kate ║ ║ 6 ║ Josh ║ ║ 7 ║ Morgan ║ ╚═══════╩══════════╝ I want to duplicate all the rows that have Kate but replace Kate in the duplicates with Kathy and then duplicate all rows containing Josh but replace the duplicates with Joshy as the username. I am not sure how to go about this. Could some MySQL expert help please?

Upvotes: 0

Views: 30

Answers (1)

trincot
trincot

Reputation: 350776

You could insert those variant records using the INSERT...SELECT syntax:

INSERT INTO mytable (UserName)
    SELECT 'Kathy'
    FROM   mytable
    WHERE  UserName = 'Kate';

Then do something similar for Josh:

INSERT INTO mytable (UserName)
    SELECT 'Joshy'
    FROM   mytable
    WHERE  UserName = 'Josh';

If you prefer one SQL statement:

INSERT INTO mytable (UserName)
    SELECT CASE UserName
               WHEN 'Kate' THEN 'Kathy'
               WHEN 'Josh' THEN 'Joshy'
           END
    FROM   mytable
    WHERE  UserName IN ('Kate', 'Josh');

In either case, the SELECT statement makes sure you get a result per instance of the duplicate (3 results for Kate and 3 for John). And for every result you provide the UDPATE statement the adjusted name to insert. So the correct number of records is inserted in this way.

Upvotes: 1

Related Questions