How do I copy a selection of rows from a mySql table and insert with just the key value changed. Can I do a select and insert in same query?
To be precise, what I want would look like this:
Table cols, ID and ISO3 are keys:
+----+------+-------------------------+
| ID | ISO3 | Text |
+----+------+-------------------------+
| 1 | ENU | A text in english |
| 2 | ENU | Another text in english |
| 3 | ENU | bla bla |
| 1 | JPN | 与えられた枠 |
+----+------+-------------------------+
After the insert I want my table to look like this:
+----+------+---------------------------+
| ID | ISO3 | Text |
+----+------+---------------------------+
| 1 | ENU | A text in english |
| 2 | ENU | Another text in english |
| 3 | ENU | bla bla |
| 1 | JPN | 与えられた枠 |
| 2 | JPN | Another text in english |
| 3 | JPN | bla bla |
+----+------+---------------------------+
INSERT INTO your_table (ID, ISO3, TEXT)
SELECT ID, 'JPN', TEXT
FROM your_table
WHERE ID IN ( list_of_ id's )
If you want to change a value in one cell, just hard-type the value instead of selecting from table (like I did with 'JPN').
If your key is auto_increment, and you have a table structure like primary_key | col1 | col2 | col3
then you can do something along the lines of
INSERT INTO table (
col1, col2, col3
) SELECT col1, col2, col3
FROM table
WHERE primary_key IN(1, 2, 45, 54, 774, 4434 /* etc */);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With