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

If you really don't want to list all the table columns like in Mark's answer, you can try this:

CREATE TEMPORARY TABLE temp_tbl SELECT * FROM items WHERE id = '9198';
SELECT @maxId := MAX(id) + 1 FROM items;
UPDATE temp_tbl SET id = @maxId;
INSERT INTO items SELECT * FROM temp_tbl;
DROP TABLE temp_tbl;

Not beautiful, not fast. But works.


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.