INSERT with auto increment

------_=_NextPart_001_01CB1879.EFAB6D38
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hi All,

=20

In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...

=20

For instance, with a two column table I could do "INSERT INTO TABLE1
VALUES('stuff')"

=20

I'm having trouble doing the same thing in mySQL...

=20

In mySQL, if I expressly give it a value, like "INSERT INTO TABLE1
VALUES(17,'stuff')" - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.

=20

The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?

=20

Need some guidance....=20

=20

Thanks!

Dave


------_=_NextPart_001_01CB1879.EFAB6D38--

Re: INSERT with auto increment

generally, it is:
INSERT INTO TABLE1 (fieldname [ , fieldname]* ) VALUES (value[, value]*)


If you don't list the columns, it assumes you are inserting all of them, so:

INSERT INTO TABLE1 (mycolumn ) VALUES ('stuff')


This will also work
INSERT INTO TABLE1 VALUES (0, 'stuff')

the auto-increment will engage on an insert of 0

- michael dykman


On Wed, Jun 30, 2010 at 1:30 PM, David Stoltz wrote:
> Hi All,
>
>
>
> In MS SQL, if the table has an identity field/primary key which is set
> to auto increment, you can leave the value out of an INSERT statement,
> and the next highest value will be automatically inserted...
>
>
>
> For instance, with a two column table I could do "INSERT INTO TABLE1
> VALUES('stuff')"
>
>
>
> I'm having trouble doing the same thing in mySQL...
>
>
>
> In mySQL, if I expressly give it a value, like "INSERT INTO TABLE1
> VALUES(17,'stuff')" - it works fine. But if I remove the 17, it says I
> don't have a matching number of columns.
>
>
>
> The field in question has a foreign key in another table, making this a
> primary key in theory, but there's nothing in myphpadmin that shows this
> as a primary key - perhaps this is the problem?
>
>
>
> Need some guidance....
>
>
>
> Thanks!
>
> Dave
>
>



--
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: INSERT with auto increment

You can choose between:

INSERT INTO TABLE1 VALUES (null,'stuff')

or

INSERT INTO TABLE1 (stuffField) VALUES ('stuff')

--
João Cândido de Souza Neto

""David Stoltz"" escreveu na mensagem
news:487E7D0857FE094590BF2DC33FE3E1080A102944@SHHS-MAIL.SHH. ORG...
Hi All,



In MS SQL, if the table has an identity field/primary key which is set
to auto increment, you can leave the value out of an INSERT statement,
and the next highest value will be automatically inserted...



For instance, with a two column table I could do "INSERT INTO TABLE1
VALUES('stuff')"



I'm having trouble doing the same thing in mySQL...



In mySQL, if I expressly give it a value, like "INSERT INTO TABLE1
VALUES(17,'stuff')" - it works fine. But if I remove the 17, it says I
don't have a matching number of columns.



The field in question has a foreign key in another table, making this a
primary key in theory, but there's nothing in myphpadmin that shows this
as a primary key - perhaps this is the problem?



Need some guidance....



Thanks!

Dave




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: INSERT with auto increment

> From: "David Stoltz"
>
> In mySQL, if I expressly give it a value, like "INSERT INTO TABLE1
> VALUES(17,'stuff')" - it works fine. But if I remove the 17, it says I
> don't have a matching number of columns.

Use NULL for the autoinsert column.

----------------
I made it a rule to forbear all direct contradictions to the
sentiments of others, and all positive assertion of my own. I even
forbade myself the use of every word or expression in the language
that imported a fixed opinion, such as "certainly," "undoubtedly,"
etc. I adopted instead of them "I conceive," "I apprehend," or "I
imagine" a thing to be so or so; or "so it appears to me at present."
When another asserted something that I thought an error, I denied
myself the pleasure of contradicting him abruptly, and of showing him
immediately some absurdity in his proposition. In answering I began by
observing that in certain cases or circumstances his opinion would be
right, but in the present case there appeared or seemed to me some
difference, etc. I soon found the advantage of this change in my
manner; the conversations I engaged in went on more pleasantly. The
modest way in which I proposed my opinions procured them a readier
reception and less contradiction. I had less mortification when I was
found to be in the wrong, and I more easily prevailed with others to
give up their mistakes and join with me when I happened to be in the
right. -- Benjamin Franklin
:::: Jan Steinman, EcoReality Co-op ::::


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: INSERT with auto increment

Awesome - thanks all for that clarification!


-----Original Message-----
From: Michael Dykman [mailto:mdykman@gmail.com]=20
Sent: Wednesday, June 30, 2010 1:42 PM
To: David Stoltz
Cc: mysql@lists.mysql.com
Subject: Re: INSERT with auto increment

generally, it is:
INSERT INTO TABLE1 (fieldname [ , fieldname]* ) VALUES (value[, value]*)


If you don't list the columns, it assumes you are inserting all of them,
so:

INSERT INTO TABLE1 (mycolumn ) VALUES ('stuff')


This will also work
INSERT INTO TABLE1 VALUES (0, 'stuff')

the auto-increment will engage on an insert of 0

- michael dykman


On Wed, Jun 30, 2010 at 1:30 PM, David Stoltz wrote:
> Hi All,
>
>
>
> In MS SQL, if the table has an identity field/primary key which is set
> to auto increment, you can leave the value out of an INSERT statement,
> and the next highest value will be automatically inserted...
>
>
>
> For instance, with a two column table I could do "INSERT INTO TABLE1
> VALUES('stuff')"
>
>
>
> I'm having trouble doing the same thing in mySQL...
>
>
>
> In mySQL, if I expressly give it a value, like "INSERT INTO TABLE1
> VALUES(17,'stuff')" - it works fine. But if I remove the 17, it says I
> don't have a matching number of columns.
>
>
>
> The field in question has a foreign key in another table, making this
a
> primary key in theory, but there's nothing in myphpadmin that shows
this
> as a primary key - perhaps this is the problem?
>
>
>
> Need some guidance....
>
>
>
> Thanks!
>
> Dave
>
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg