Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Clarification on Insert with Select * ???

Clarification on Insert with Select * ???

Clarification on Insert with Select * ???

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.

RE: Clarification on Insert with Select * ???

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-sele...

Bye, Olaf.

RE: Clarification on Insert with Select * ???

thumbsup2Thank you. Just was I was looking for.

RE: Clarification on Insert with Select * ???


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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close