Add row that is duplicate except for one column
I need to write a query to add a duplicate row to a table a bit like this...
INSERT INTO `<table>` (column1, column2, ...) -- Not IDENTITY columns
SELECT column1, column2, ... FROM ...
except that the first column must have a different value in the copied row.
i.e.
┌─────┬────────┬────────┐
|name | weight | height |
├─────┼────────┼────────┤
│ Bob │ 100 │ 150 │
│ │ │ │
└─────┴────────┴────────┘
To
开发者_开发百科┌─────┬────────┬────────┐
|name | weight | height |
├─────┼────────┼────────┤
│ Bob │ 100 │ 150 │
│ Jim │ 100 │ 150 │
└─────┴────────┴────────┘
I can't simply insert an identical row, and then update that column, because the "name" column is unique.
Obviously in reality my table has many more columns than this example.
Have you tried:
INSERT INTO `<table>` (column1, column2, ...)
SELECT 'Jim', column2, ... FROM ... WHERE ...
精彩评论