PHP MySQL Copy a row within the same table... with a Primary and Unique key

duplicates, mysql, php, row

Solution

Select all columns explicitly, except the id column:

INSERT INTO items
(col1, col2, ..., coln)
SELECT col1, col2, ..., coln
FROM items
WHERE id = '9198'

Your next question will probably be:

Is there a way to do this without listing all the columns explicitly?

Answer: No, I don't think so.

Problem

My table has two keys, one is an auto incrementing id (PRIMARY), the other is the name of the item (UNIQUE). Is it possible to duplicate a row within this same table? I have tried: ``` INSERT INTO items SELECT * FROM items WHERE id = '9198' ``` This gives the error `Duplicate entry '9198' for key 'PRIMARY'` I have also tried: ``` INSERT INTO items SELECT * FROM items WHERE id = '9198' ON DUPLICATE KEY UPDATE id=id+1 ``` Which gives the error `Column 'id' in field list is ambiguous` And as far as the item name (UNIQUE) field goes, is there a way to append `(Copy)` to the item name, since this field must also be unique?

Original source