CODEWITHSUNDEEP
sqlms-access
Urbycoz
Urbycoz

Reputation: 7421

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.

Upvotes: 1

Views: 2060

Answers (1)

Mat
Mat

Reputation: 206709

Have you tried:

INSERT INTO `<table>` (column1, column2, ...)
SELECT 'Jim', column2, ... FROM ... WHERE ...

Upvotes: 3

Related Questions