Clarification on Insert with Select * ??? 1

[BobMCT]

I'm attempting to resolve an issue with another devs mysql statement and I cannot find any clear answer in the various online docs I'm reading.
I'm hoping one or more of you SQL guru's might be able to enlighten me?

Let say tableA has an auto_increment key and our task is to retrieve a record from tableA based on a supplied key value and then insert it into the same table using an auto-assigned new key. During the transition certain fields moving tableA to the new record must be changed/nulled so the majority of the data is the same except that the access key is new and certain fields are nulled/changed.

Currently the program has something like this:

INSERT INTO tableA SET SELECT * from tableA WHERE tableA.key = 'whatever'

First, is this anywhere near correct in its format and intent?

Second, if it is then is there a way to change/null those certain fields during the move?
or must one then perform an UPDATE SET based on the new/retrieved key?

I hope I'm explaining this sufficiently clear to make sense.
I usually keep my SQL queries simpler to avoid things like this. But if there's a way it makes for good learning as well.

Thanks in advance.

 

[Olaf Doschke]

1. Remove SET, SET only is a keyword for UPDATEs.
2. If you want to have NULL for some fields you better not use * but an explicit list of fields and put in NULL for those you want null.

eg table has fields id, name, updatedat field and updatedat should be null for new rows, id should be automatic, then you do

INSERT INTO table (name,updatedat) SELECT name, NULL FROM table where id = X

So you specify the field list excluding the automatic key column and sepcify the fields or NULL in the fieldlist of the following select.
It is explained in more detail here:

http://dev.mysql.com/doc/refman/5.7/en/insert-select.html

Bye, Olaf.

 

[BobMCT]

Thank you. Just was I was looking for.

 

[Olaf Doschke]

Fine,

by the way you can also skip fields you defined with default values. Eg if updatedat was NULL by default, the insert would even be shortened to just copying the name.

Bye, Olaf.