Copy row with one field changed and new autogenerated id in MySQL

In an attempt to copy all the columns of an existing row where only ONE column will change (called "field") and the auto generated primary key field (called "id") should be generated as usual by the insert statement, my naive approach is this:

drop table if exists temp1;
create table temp1 as
(
SELECT *
FROM original
WHERE field="old value"
)
;

# Update one of the many fields to a new value
UPDATE temp1
SET field = "new value"
;

# Getting rid of the primary key in the hope that a new one will be auto generated in the following insert
ALTER TABLE temp1 DROP id;

# Fails with syntax error. How can I specify "generate new id" ?
INSERT INTO original
SELECT NULL as id, *
FROM temp1
;

This fails with syntax error as it is not allowed to put NULL as id in the last select statement. So how would I go about doing this? Is there a simpler way?

Solution

SELECT * is antipattern. You should explicitly set columns:

INSERT INTO original(col1, col2, ...)  --skip id column
SELECT col1, col2, ...
FROM temp1;

Or even better skip the temp1 table at all. Single statement solution:

INSERT INTO original(col1, col2, ..., field) -- skip id column
SELECT col1, col2, ..., 'new_value'
FROM original
WHERE field='old value';

To get what you want you need mechanism like Oracle DEFAULT Values On Explicit NULLs.

But even then you need to drop column from temp1, because:

INSERT INTO original  -- only N column
SELECT NULL as id, *  -- this will return N+1 columns
FROM temp1;

So you have to use:

ALTER TABLE temp1 DROP COLUMN id;
-- now columns match

INSERT INTO original  -- only N column
SELECT NULL as id, *  -- this will return N columns and
                      -- NULL is handled by DEFAULT ON NULL
FROM temp1;