[SOLVED] Difference between SET and VALUES

[Xurion]

Hi all,

 

I'm having a debate at my office with a friend who says you shouldn't use the following to input:

 

INSERT INTO mytable SET column='value', columnb='value'

 

I've always used this method (as I think it's easier to look at) but my friend says you should only use the following method to input:

 

INSERT INTO mytable (column, columnb) VALUES ('value', 'value')

 

Is this correct or is his personal opinion clouding his knowledge?

 

Thanks in advance

 

Xur~

[fenway]

The latter is more useful, because you can create multi-valued insert statements.

 

The only reason the former exists is for compatibility and similiarty to the update syntax.

[neoform]

What do you mean by "create multi-valued insert statements"?

[Mr. Despair]

How about using just: "INSERT INTO mytable VALUES ('value', 'value')"

 

A lot of times I just skip the listing of the column names because I'm lazy, but I guess I shouldn't?

[PFMaBiSmAd]

What do you mean by "create multi-valued insert statements"?

 

From the mysql manual -

 

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

 

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

 

 

[neoform]

ah, k, I was confusing 'value' with field.

[fenway]

How about using just: "INSERT INTO mytable VALUES ('value', 'value')"

 

A lot of times I just skip the listing of the column names because I'm lazy, but I guess I shouldn't?

No, you should NEVER do that... good luck if you ever change the order of the fields or add a new one!

 

That's the only reason I don't like this syntax :-(

[crtreedude]

How about using just: "INSERT INTO mytable VALUES ('value', 'value')"

 

A lot of times I just skip the listing of the column names because I'm lazy, but I guess I shouldn't?

 

Only if you wish to experience what your user name suggest, despair. Clean code will help you preserve what little sanity remains. Trust me on this. When a project gets really large, with lots of hands in it, locking everything down tight is the only way to not lose your mind.

 

I remember restoring a SQL database once and the field orders actually changed.    I would suggest that you shouldn't ever assume the order of fields. There is no guarantee.