Interaction design • webdevelopment • web art • photography

December 2004

There's an INSERT INTO ... SET ... syntax? I had no idea.

So instead of this...
INSERT INTO sometable
     ( field1, field2, field3 )
     ( 'value1', 'value2', 'value3' )'s actually possible to use this:
INSERT INTO sometable
   SET field1 = 'value1',
       field2 = 'value2',
       field3 = 'value3'

Calm_Pear wrote on 2004/12/08:
SET? thats odd... thats a syntax for an UPDATE... It looks like the guys that are working on MYSQL are becoming more like the microsoft people... any idiot should be able to get some data in the database... not good if you'd ask me

Milo wrote on 2004/12/09:
I do like the syntax though... Less work to add or remove fields to or from a query.

But using it might be tricky, since it's only "supported in MySQL 3.22.10 or later".

Low wrote on 2004/12/09:
I repeat: wha-hey!

Ruben wrote on 2004/12/09:
"Any idiot" indeed. I tried that once in SQL, years ago.

Kethinov wrote on 2004/12/09:
You're welcome, Milo. ;)

Milo wrote on 2004/12/09:
Aint it great how when one person looks at someone else's code, both can learn something new :)

digi wrote on 2006/08/11:
why would you think there is anything wrong with that syntax? This makes it much easier than INSERT INTO VALUES, you don't have to count through each of the values fields to find the value that corresponds to the column, this is a great syntax addon.

the hatter wrote on 2006/10/28:
We think there's something wrong with this syntax because it is not part of the SQL specifications, as far as I can see. Not that language extensions by themself are a bad thing, but one that replicates an extraordinarily well known method seems silly. Most people use DBs through a language API, if you want to write nicer queries, there's nothing to stop someone adding this sort of extension to their API.

Plague wrote on 2007/04/30:
this is in the 'INSERT' doc on mysql offical pages...

this makes it easyer to do loops on the values and their names... ie. php's foreach(), instead of having 2, the names and values seperate, you can now just do 1 loop seeing that the names and values are right next to each other.

this also makes reading and matching the things a hell lot easyer...

you can now tell if you have 6 names but only 5 values, without counting them all :P wrote on 2007/07/25:
well, this syntax sure makes thigs easier to code in php etc., and that's a big plus...
but I think you can only use this syntax if you are sure that your code won't be used under diffrent db engine

JVirus wrote on 2008/03/31:
This is a nice way and works well. 1 word of caution though, if you want your code to be more universal I would stick to the INSERT INTO and VALUES since this will work both in access and SQL. Obviously most of you will be like, WHAT! ACCESS?!?!?!? But for my company our development is in access then we port that to SQL, I know... I dont have the power to make them change, but for any visitors that might be in the same boat, just a good thing to remember.

Liam wrote on 2008/05/05:
I have to second JVirus on that. I've just been working on porting some MySQL code to use PostgreSQL, which uses the standard syntax. Using syntax specific to one vendor, when there's a standard that achieves the same thing, is not the smartest move.

Liam wrote on 2008/05/05:
Also worth noting while I'm dealing with the pain of porting from one DB to another - don't use MySQL's double quotes for strings - not standard, not portable, not good :(

Tushar Mahajan wrote on 2009/09/17:
Using first option we can insert multiple values LIKE

INSERT INTO sometable
( field1, field2, field3 )
( 'value1', 'value2', 'value3' ),
( 'value1', 'value2', 'value3' ),
( 'value1', 'value2', 'value3' );

ME wrote on 2009/10/26:
I just started a new job and ran across this in some code today. I've worked with various dbs over the year but never knew of this syntax. It disgusts me since it's not the standard. However, I will say it seems more logical and nicer looking. I'm still not going to adopt it though.

Nilesh Gupta wrote on 2009/11/22:
this patern is very good for using OOps
i like this patertn to using this commands insert into table_name set fieldname=value it is a very good type of this

superdude wrote on 2010/05/25:
While I agree about standards, I think that if the query will only be used on MySQL, it is perfectly valid. If you are working on a larger project that might be used with various DBs, you should probably be using some kind of DB abstraction layer that takes care of the details for you.

sousgarden wrote on 2010/10/12:
This function is crap.
I have code now (not my) which have to work on mysql 5.1, previous DB was 5.0.
If I try now to insert with an auto-increment primary I get an error. Cause the coder was sooo an idiot and make following insert:
And the primary column now try to insert NULL what not work and have NOT to WORK!!!!!
That this shit work before was not good.

justDroppingBy wrote on 2010/11/22:
@sousgarden: this code is a good way of inserting values in a table; faster and easier.

My question is, why would you insert a value to an AI column? AND whether you use the old code or this to insert a NULL value in an AI column it'll still give you errors.

erdincgc wrote on 2011/03/15:
The first thing you need to learn ; be polite!
The second ; In both syntax you'd better stop setting any Autoincrement field. Your table's index field's auto increment parameters might be wrong i guess. And about the insert into thing; useful for small queries if it's not slower (is it?).

ferdly wrote on 2011/04/01:
Calm_Pear says, "any idiot should be able to get some data in the database... not good if you'd ask me"

Well, this is the quintessence of the 'feature enhancements are a threat to my job' mentality.

How about you imagine how much easier it will be to dynamically construct and validate INSERT statements within a Database Abstraction Layer that makes your skills even *more* valuable?

ferdly wrote on 2011/04/01:
Furthermore... I think this syntax *should* be added to standard SQL, along with "INSERT... ON DUPLICATE KEY UPDATE" these are natural and intrinsic *database* tasks, not API tasks... why the 1992 folks didn't see that clarity and elegance of making the UPDATE syntax and INSERT syntax as parallel as possible, *then* adding the standard syntax as a 'power load' alternative is beyond me.

I respect those that refuse to use any proprietary SQL constructs for portability -- and those that choose to use proprietary constructs have an added documentation obligation to identify where those constructs were used for eventual portablity. But for me I would rather endure one-time, upfront documentation task and leverage the power of the proprietary construct continually.

I also believe that *using* the best alternative sql constructs make it *more* likely that there will be a 'critical mass' behind eventally including them in the standard.

Further, 'disgust' with MySQL? MySQL is not the only vendor deploying alternative SQL constructs -- I think Oracle has an equivalent to "INSERT... ON DUPLICATE KEY UPDATE" that is proprietary, and MS SQL has a great number of these -- this is further muddied by the fact that by co-opting in the popular consciousness that "SQL" is not a Language, but a product of Microsoft Corporation. (Admittely, it would seem Microsoft has done the best job with Cross-Tabs, but I dunno much there?...)

Not a Flame against MS SQL or any other, so don't scorch me back :-) -- just pointing out that whatever tool you use, you need to be aware of what code will be universally portable and what will not.

Happy coding...

Matt wrote on 2011/07/20:
I must be an idiot, I like it, I like anything that makes life easier, I guess if I was smart I would write all my code in assembly instead of PHP and MySQL

Ivan Ivkovich wrote on 2011/09/15:
The reason this was implemented :

After altering the table, you have to edit every single webpage containing query with 'INSERT INTO table VALUES' because you have to add '' if there is a new collumn in every query.
If you have SET to a specific column, you don't have to worry about the columns you won't fill with data.