Duplicating rows in sql query

I have a table containing several fields. The primary key is userId. Currently the user id column contains values '1,2,3,4...开发者_StackOverflowetc' like so:

+------+
|userId|
+------+
|  1   |
|  2   |
|  3   |
|  4   | 
...etc

I now want to add new rows ending in a,b,c, like so:

+------+
|userId|
+------+
|  1   |
|  1a  |
|  1b  |
|  1c  | 
|  2   |
|  2a  |
|  2b  |
|  2c  | 
...etc

The new rows should be identical to their parent row, except for the userId. (i.e. 1a,1b & 1c should match 1)

Also I can't guarantee that there won't already be a few 'a', 'b' or 'c's in userid column.

Is there a way to write an sql query to do this quickly and easily?

---

DON'T DO IT you will run into more problems than the one you are trying to solve!

add a new column to store the letter and make the primary key cover the original UserId and this new column.

If you ever just want the userId, you need to split the letter portion off, which will be expensive for your query and be a real pain.

---

I agree with KM. I'm not sure why you're creating these duplicate/composite IDs, but it feels like an uncomfortable direction to take.

That said, there is only really one obsticle to overcome; Apparently you can't select from and insert into the same table in MySQL.

So, you need to insert into a Temporary Table first, then insert into the real table...

CREATE Temporary TABLE MyNewUserIDs (
  UserID    VARCHAR(32)
)

INSERT INTO
  myNewUserIDs
SELECT
  CONCAT(myTable.UserID, suffix.val)
FROM
  myTable
INNER JOIN
  (SELECT 'A' as val UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D') AS suffix
    ON RIGHT(myTable.UserID, 1) <> Suffix.val
WHERE
  NOT EXISTS (SELECT * FROM myTable AS lookup WHERE UserID = CONCAT(myTable.UserID, suffix.val))

INSERT INTO
  myTable
SELECT
  UserID
FROM
  MyNewUserIDs

Depending on your environment, you may want to look into locking the tables, so that changes are not made between creating the list of IDs and inserting them into your table.

---

This is quite simple from a SQL perspective to generate the extra rows: I'll do that here

@Km's answer tells you how to store it as 2 distinct values which I've assumed here. Feel free to concatenate userid and suffix if you prefer.

INSERT myTable (userid, suffix, co11, col2, ...coln)
SELECT M.userid, X.suffix, M.col1, M.col2, ..., M.coln
FROM
    myTable M
    CROSS JOIN
    (SELECT 'a' AS Suffix UNION ALL SELECT 'b' UNION ALL SELECT 'c') X
WHERE
    NOT EXISTS (SELECT *
          FROM
             MyTable M2
          WHERE
             M2.userid = M.userid ANS M2.Suffix = X.Suffix)