There's an INSERT INTO ... SET ... syntax? I had no idea.
So instead of this...
INSERT INTO sometable
( field1, field2, field3 )
VALUES
( 'value1', 'value2', 'value3' )
...it's actually possible to use this:
INSERT INTO sometable
SET field1 = 'value1',
field2 = 'value2',
field3 = 'value3'
But using it might be tricky, since it's only "supported in MySQL 3.22.10 or later".
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
but I think you can only use this syntax if you are sure that your code won't be used under diffrent db engine
INSERT INTO sometable
( field1, field2, field3 )
VALUES
( 'value1', 'value2', 'value3' ),
( 'value1', 'value2', 'value3' ),
( 'value1', 'value2', 'value3' );
i like this patertn to using this commands insert into table_name set fieldname=value it is a very good type of this
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:
INSERT INTO table SET COL_PRIMARY="", COL_TEXT1="blabla";
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.
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.
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?).
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?
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...
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.