Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at the jQuery Conference 2009 (Cambridge, MA) with: Bob Bonifield
Ben Nadel at the jQuery Conference 2009 (Cambridge, MA) with: Bob Bonifield ( @bbonifield )

Using The INSERT INTO ... SET Syntax In MySQL

By Ben Nadel on
Tags: SQL

A little while back, I was doing a code review for one my colleagues - Bryan Stanley - when I saw him using an INSERT syntax that I had never seen before. Rather than the traditional INSERT INTO ... VALUES syntax, he was using an INSERT INTO ... SET syntax. This appears to be a MySQL-specific extension to the SQL syntax; but, it's pretty darned cool.

Here's what it would look like to insert a new "friend" record:

  • -- Create a new friend record.
  • INSERT INTO
  • friend
  • SET
  • name = 'Kim',
  • isBFF = true,
  • updatedAt = UTC_TIMESTAMP()
  • ;
  •  
  • -- Return the newly-generated primary-key.
  • SELECT
  • ( @@Identity ) AS id
  • ;

The most obvious benefit of this syntax, in my opinion, is that is puts the column names and insert values in very close proximity. This completely eliminates the possibility of providing column names and values in an incorrect order. It also cuts down on the cognitive overhead of understanding the query as you don't need to jump back-and-forth between two different portions of the statement.

So far, I've started using this syntax in some migration scripts. But, I've yet to fully embrace it for the core areas of the application. Since it's not a "standard" part of the SQL syntax, I always feel a little hesitant about embracing it. But, that's just an emotional response; if I'm going to use a particular database storage engine, I might as well benefit from that choice rather than feel limited by it.



Looking For A New Job?

Ooops, there are no jobs. Post one now for only $29 and own this real estate!

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

@Michael,

Right?! I can't tell you how many queries I've debugging were the column names and the values were in different orders :D

Ben-

Something might be up with your comment subscription. I didn't get an update to any of this.

And @@Identity is a SQL Server thing, but you can replicate it in other DBMSs, I'm sure. It's just a handle to the last autoincrementing Identity value added to a table.

Assuming you use auto-incrementing values that don't represent table data, thus allowing for relational integrity issues. As opposed to a natural key made up of the fields that naturally define a unique set of data. But, sadly, that's a fight long lost.

@Matt,

Sorry, I think I miscommunicated. I understand the "@@Identity" portion. In fact, MySQL supports that as well (which is much nicer on the eyes than that LAST_INSERT_ID() mumbo-jumbo). What I meant was that I didn't understand what the "OUTPUT" token did.

Taking a look at the docs: https://msdn.microsoft.com/en-us/library/ms177564.aspx

.... hmm, looks to me a MSSQL thing that MySQL doesn't have parity for. But, at least they each expose the @@Identity for the last inserted record, which is typically what I need.

@Matt,

FYI, I just checked my SMTP service and it looks like you should have gotten an email about the comment I just left. So, if you're not getting things, it might be going to spam?

@Evagoras,

To be honest, I've __never__ understood the scope-safety argument. I've seen that pointed out before, but I've never understood the practical implications.

@Matt,

By this model, Sql can not go wrong, it is said that in the write speed than the'Insert into (values) 'fast.

Hello My Dear,
I am Evelina from Lollipss Angele's in United State of America am testifying of a company that have human feeling and God fearing person, who i meet in yahoo answer in not less than 48 hours Ago,i was looking for a private Loan company where i can get a fast loan of about 960,000us dollars .so i saw someone testifying for a company that give her a loan of about 960,000us dollars within 24 banking hours' Then i contact the company because i don't believe in testimony, so i just say let me try if the company can help out of my financial problem, Mr.David Walker mail me and he said i should fill the form that was send to me that will enable them proceed on the loan immediately and i did and i follow all the process with they give me , so to my greatest surprise the loan was transfer to my bank account not less than 24 hour. I don't know the kind of financial problem that you are having or you are have a dream of having your own house. this is the company that can make your dream come true email them today at (davidwalkerloancompany2016@yahoo.com) and you will be happy as i am today.

Thanks

Hello My Dear,
I am Evelina from Lollipss Angele's in United State of America am testifying of a company that have human feeling and God fearing person, who i meet in yahoo answer in not less than 48 hours Ago,i was looking for a private Loan company where i can get a fast loan of about 960,000us dollars .so i saw someone testifying for a company that give her a loan of about 960,000us dollars within 24 banking hours' Then i contact the company because i don't believe in testimony, so i just say let me try if the company can help out of my financial problem, Mr.David Walker mail me and he said i should fill the form that was send to me that will enable them proceed on the loan immediately and i did and i follow all the process with they give me , so to my greatest surprise the loan was transfer to my bank account not less than 24 hour. I don't know the kind of financial problem that you are having or you are have a dream of having your own house. this is the company that can make your dream come true email them today at (davidwalkerloancompany2016@yahoo.com) and you will be happy as i am today.

Thanks

Hello My Dear,
I am Evelina from Lollipss Angele's in United State of America am testifying of a company that have human feeling and God fearing person, who i meet in yahoo answer in not less than 48 hours Ago,i was looking for a private Loan company where i can get a fast loan of about 960,000us dollars .so i saw someone testifying for a company that give her a loan of about 960,000us dollars within 24 banking hours' Then i contact the company because i don't believe in testimony, so i just say let me try if the company can help out of my financial problem, Mr.David Walker mail me and he said i should fill the form that was send to me that will enable them proceed on the loan immediately and i did and i follow all the process with they give me , so to my greatest surprise the loan was transfer to my bank account not less than 24 hour. I don't know the kind of financial problem that you are having or you are have a dream of having your own house. this is the company that can make your dream come true email them today at (davidwalkerloancompany2016@yahoo.com) and you will be happy as i am today.

Thanks