CURRENT insert ID

------=_NextPart_000_0076_01CBB960.2EA4C3A0
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Here it is in a nutshell:

=20

I have a field that needs to be set equal to the auto-increment ID as a =
record is entered. I donâ€=99t know how to do this without a =
subsequent UPDATE (which I can do with a trigger). Is there any way to =
avoid the cost of an UPDATE?

=20

Hereâ€=99s a more concrete description of the problem:

=20

CREATE TABLE t (

id INT(11) AUTO-INCREMENT PRIMARY,

xxx INT(11)

);

=20

When a record is added to table `t`, I need to set `xxx` to the value =
generated for `id`. (`xxx` might be changed later.)

=20

Is there anything clever I can do?

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp=20

Web site: www.the-infoshop.com

=20


------=_NextPart_000_0076_01CBB960.2EA4C3A0--

Re: CURRENT insert ID

I think an ON INSERT TRIGGER would take care of this; can't think of
any other way. =A0Using last_insert_id() in the argument list would
likely yield you the previous value (which might not even related to
your table.

Having siad that.. =A0 odd requirement.

=A0- michael dykman

ps -- sorry for the duplicate Jerry, reply-to policy on this list is
forever tripping me up.


>
> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz wrote:
>> Here it is in a nutshell:
>>
>>
>>
>> I have a field that needs to be set equal to the auto-increment ID as a =
record is entered. I don=92t know how to do this without a subsequent UPDAT=
E (which I can do with a trigger). Is there any way to avoid the cost of an=
UPDATE?
>>
>>
>>
>> Here=92s a more concrete description of the problem:
>>
>>
>>
>> CREATE TABLE t (
>>
>> id INT(11) AUTO-INCREMENT PRIMARY,
>>
>> xxx INT(11)
>>
>> );
>>
>>
>>
>> When a record is added to table `t`, I need to set `xxx` to the value ge=
nerated for `id`. (`xxx` might be changed later.)
>>
>>
>>
>> Is there anything clever I can do?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> E-mail: =A0 jerry@gii.co.jp
>>
>> Web site: =A0 www.the-infoshop.com
>>
>>
>>
>>
>
>
>
> --
> =A0- michael dykman
> =A0- mdykman@gmail.com
>
> =A0May the Source be with you.
>



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

=A0May 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

RE: CURRENT insert ID

>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@gmail.com]
>Sent: Friday, January 21, 2011 11:50 AM
>To: MySql
>Subject: Re: CURRENT insert ID
>
>I think an ON INSERT TRIGGER would take care of this; can't think of
>any other way. Using last_insert_id() in the argument list would
>likely yield you the previous value (which might not even related to
>your table.
>
>Having siad that.. odd requirement.
>
[JS] You don't know the half of it.

Thanks.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



> - michael dykman
>
>ps -- sorry for the duplicate Jerry, reply-to policy on this list is
>forever tripping me up.
>
>
>>
>> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz wrote:
>>> Here it is in a nutshell:
>>>
>>>
>>>
>>> I have a field that needs to be set equal to the auto-increment ID as a
>record is entered. I don't know how to do this without a subsequent UPDATE
>(which I can do with a trigger). Is there any way to avoid the cost of an
>UPDATE?
>>>
>>>
>>>
>>> Here's a more concrete description of the problem:
>>>
>>>
>>>
>>> CREATE TABLE t (
>>>
>>> id INT(11) AUTO-INCREMENT PRIMARY,
>>>
>>> xxx INT(11)
>>>
>>> );
>>>
>>>
>>>
>>> When a record is added to table `t`, I need to set `xxx` to the value
>generated for `id`. (`xxx` might be changed later.)
>>>
>>>
>>>
>>> Is there anything clever I can do?
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>> Jerry Schwartz
>>>
>>> Global Information Incorporated
>>>
>>> 195 Farmington Ave.
>>>
>>> Farmington, CT 06032
>>>
>>>
>>>
>>> 860.674.8796 / FAX: 860.674.8341
>>>
>>> E-mail: jerry@gii.co.jp
>>>
>>> Web site: www.the-infoshop.com
>>>
>>>
>>>
>>>
>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>>
>> May the Source be with you.
>>
>
>
>
>--
> - 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=jerry@gii.co.jp





--
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: CURRENT insert ID

>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Friday, January 21, 2011 11:56 AM
>To: 'Michael Dykman'; 'MySql'
>Subject: RE: CURRENT insert ID
>
>>-----Original Message-----
>>From: Michael Dykman [mailto:mdykman@gmail.com]
>>Sent: Friday, January 21, 2011 11:50 AM
>>To: MySql
>>Subject: Re: CURRENT insert ID
>>
>>I think an ON INSERT TRIGGER would take care of this; can't think of
>>any other way. Using last_insert_id() in the argument list would
>>likely yield you the previous value (which might not even related to
>>your table.
>>
[JS] Alas, you cannot update a record in a trigger if the record is in the
same table as the trigger.


CREATE TRIGGER xx AFTER UPDATE ON t
FOR EACH ROW
UPDATE t SET f1 = 7;


That's illegal.

Right now, I'm stumped.

>>Having siad that.. odd requirement.
>>
>[JS] You don't know the half of it.
>
>Thanks.
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: jerry@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>
>> - michael dykman
>>
>>ps -- sorry for the duplicate Jerry, reply-to policy on this list is
>>forever tripping me up.
>>
>>
>>>
>>> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz wrote:
>>>> Here it is in a nutshell:
>>>>
>>>>
>>>>
>>>> I have a field that needs to be set equal to the auto-increment ID as a
>>record is entered. I don't know how to do this without a subsequent UPDATE
>>(which I can do with a trigger). Is there any way to avoid the cost of an
>>UPDATE?
>>>>
>>>>
>>>>
>>>> Here's a more concrete description of the problem:
>>>>
>>>>
>>>>
>>>> CREATE TABLE t (
>>>>
>>>> id INT(11) AUTO-INCREMENT PRIMARY,
>>>>
>>>> xxx INT(11)
>>>>
>>>> );
>>>>
>>>>
>>>>
>>>> When a record is added to table `t`, I need to set `xxx` to the value
>>generated for `id`. (`xxx` might be changed later.)
>>>>
>>>>
>>>>
>>>> Is there anything clever I can do?
>>>>
>>>>
>>>>
>>>> Regards,
>>>>
>>>>
>>>>
>>>> Jerry Schwartz
>>>>
>>>> Global Information Incorporated
>>>>
>>>> 195 Farmington Ave.
>>>>
>>>> Farmington, CT 06032
>>>>
>>>>
>>>>
>>>> 860.674.8796 / FAX: 860.674.8341
>>>>
>>>> E-mail: jerry@gii.co.jp
>>>>
>>>> Web site: www.the-infoshop.com
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>> - michael dykman
>>> - mdykman@gmail.com
>>>
>>> May the Source be with you.
>>>
>>
>>
>>
>>--
>> - 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=jerry@gii.co.jp
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
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: CURRENT insert ID

I made a typo in my previous message.

>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Friday, January 21, 2011 12:20 PM
>To: 'Jerry Schwartz'; 'Michael Dykman'; 'MySql'
>Subject: RE: CURRENT insert ID
>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>Sent: Friday, January 21, 2011 11:56 AM
>>To: 'Michael Dykman'; 'MySql'
>>Subject: RE: CURRENT insert ID
>>
>>>-----Original Message-----
>>>From: Michael Dykman [mailto:mdykman@gmail.com]
>>>Sent: Friday, January 21, 2011 11:50 AM
>>>To: MySql
>>>Subject: Re: CURRENT insert ID
>>>
>>>I think an ON INSERT TRIGGER would take care of this; can't think of
>>>any other way. Using last_insert_id() in the argument list would
>>>likely yield you the previous value (which might not even related to
>>>your table.
>>>
>[JS] Alas, you cannot update a record in a trigger if the record is in the
>same table as the trigger.
>
>
>CREATE TRIGGER xx AFTER UPDATE ON t
>FOR EACH ROW
> UPDATE t SET f1 = 7;
>
>
[JS] That should have read "AFTER INSERT"; but it's still illegal.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com








--
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: CURRENT insert ID

I can´t think about how useful for you would be to have two fields with the
same value.

--
João Cândido de Souza Neto

""Jerry Schwartz"" escreveu na mensagem
news:007501cbb98a$177acba0$467062e0$@co.jp...
Here it is in a nutshell:



I have a field that needs to be set equal to the auto-increment ID as a
record is entered. I don't know how to do this without a subsequent UPDATE
(which I can do with a trigger). Is there any way to avoid the cost of an
UPDATE?



Here's a more concrete description of the problem:



CREATE TABLE t (

id INT(11) AUTO-INCREMENT PRIMARY,

xxx INT(11)

);



When a record is added to table `t`, I need to set `xxx` to the value
generated for `id`. (`xxx` might be changed later.)



Is there anything clever I can do?



Regards,



Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp

Web site: www.the-infoshop.com






--
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: CURRENT insert ID

--0016e644cb5c8d850a049a5ec3d6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

@Joao - I'm currently building a database out right now that has this
scenario. One field can be the primary key, that has a purpose for holding
the record id, another field can hold the value. Let say there are two
fields, id, s_id. Initially, you insert a record and `id` is now 100 and yo=
u
update s_id to be 100. But for whatever reason, later down the road you
need s_id to be 200. You can just update the s_id field instead of deleting
the entire record and inserting an entire new one with X amount of fields.
Updating one field is a lot less work than deleting and inserting. I have m=
y
tables set up so I won't have to use the primary key for queries, I will
only use the s_id field.

2011/1/21 Jo=E3o C=E2ndido de Souza Neto

> I can=B4t think about how useful for you would be to have two fields with=
the
> same value.
>
> --
> Jo=E3o C=E2ndido de Souza Neto
>
> ""Jerry Schwartz"" escreveu na mensagem
> news:007501cbb98a$177acba0$467062e0$@co.jp...
> Here it is in a nutshell:
>
>
>
> I have a field that needs to be set equal to the auto-increment ID as a
> record is entered. I don't know how to do this without a subsequent UPDAT=
E
> (which I can do with a trigger). Is there any way to avoid the cost of an
> UPDATE?
>
>
>
> Here's a more concrete description of the problem:
>
>
>
> CREATE TABLE t (
>
> id INT(11) AUTO-INCREMENT PRIMARY,
>
> xxx INT(11)
>
> );
>
>
>
> When a record is added to table `t`, I need to set `xxx` to the value
> generated for `id`. (`xxx` might be changed later.)
>
>
>
> Is there anything clever I can do?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
> E-mail: jerry@gii.co.jp
>
> Web site: www.the-infoshop.com
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Ddsteplight@gmail.=
com
>
>


--=20
----------------------------------------------
"May the Source be with you."

--0016e644cb5c8d850a049a5ec3d6--

Re: CURRENT insert ID

Ok, you must have your own reasons to do that.

The fact is: You can´t set the auto_incremente value field to another field
in the same table and record even in a trigger.

So, the best way is a second update.

--
João Cândido de Souza Neto

"Darryle Steplight" escreveu na mensagem
news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-GjM@mail.gmai l.com...
@Joao - I'm currently building a database out right now that has this
scenario. One field can be the primary key, that has a purpose for holding
the record id, another field can hold the value. Let say there are two
fields, id, s_id. Initially, you insert a record and `id` is now 100 and you
update s_id to be 100. But for whatever reason, later down the road you
need s_id to be 200. You can just update the s_id field instead of deleting
the entire record and inserting an entire new one with X amount of fields.
Updating one field is a lot less work than deleting and inserting. I have my
tables set up so I won't have to use the primary key for queries, I will
only use the s_id field.

2011/1/21 João Cândido de Souza Neto

> I can´t think about how useful for you would be to have two fields with
> the
> same value.
>
> --
> João Cândido de Souza Neto
>
> ""Jerry Schwartz"" escreveu na mensagem
> news:007501cbb98a$177acba0$467062e0$@co.jp...
> Here it is in a nutshell:
>
>
>
> I have a field that needs to be set equal to the auto-increment ID as a
> record is entered. I don't know how to do this without a subsequent UPDATE
> (which I can do with a trigger). Is there any way to avoid the cost of an
> UPDATE?
>
>
>
> Here's a more concrete description of the problem:
>
>
>
> CREATE TABLE t (
>
> id INT(11) AUTO-INCREMENT PRIMARY,
>
> xxx INT(11)
>
> );
>
>
>
> When a record is added to table `t`, I need to set `xxx` to the value
> generated for `id`. (`xxx` might be changed later.)
>
>
>
> Is there anything clever I can do?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
> E-mail: jerry@gii.co.jp
>
> Web site: www.the-infoshop.com
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=dsteplight@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: CURRENT insert ID

You don't need to do an update:

....

new.xxx =3D new.id
....

On Fri, Jan 21, 2011 at 12:20 PM, Jerry Schwartz wrote:
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>Sent: Friday, January 21, 2011 11:56 AM
>>To: 'Michael Dykman'; 'MySql'
>>Subject: RE: CURRENT insert ID
>>
>>>-----Original Message-----
>>>From: Michael Dykman [mailto:mdykman@gmail.com]
>>>Sent: Friday, January 21, 2011 11:50 AM
>>>To: MySql
>>>Subject: Re: CURRENT insert ID
>>>
>>>I think an ON INSERT TRIGGER would take care of this; can't think of
>>>any other way. =A0Using last_insert_id() in the argument list would
>>>likely yield you the previous value (which might not even related to
>>>your table.
>>>
> [JS] Alas, you cannot update a record in a trigger if the record is in th=
e
> same table as the trigger.
>
>
> CREATE TRIGGER xx AFTER UPDATE ON t
> FOR EACH ROW
> =A0UPDATE t SET f1 =3D 7;
>
>
> That's illegal.
>
> Right now, I'm stumped.
>
>>>Having siad that.. =A0 odd requirement.
>>>
>>[JS] You don't know the half of it.
>>
>>Thanks.
>>
>>Regards,
>>
>>Jerry Schwartz
>>Global Information Incorporated
>>195 Farmington Ave.
>>Farmington, CT 06032
>>
>>860.674.8796 / FAX: 860.674.8341
>>E-mail: jerry@gii.co.jp
>>Web site: www.the-infoshop.com
>>
>>
>>
>>> =A0- michael dykman
>>>
>>>ps =A0-- sorry for the duplicate Jerry, reply-to policy on this list is
>>>forever tripping me up.
>>>
>>>
>>>>
>>>> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz wro=
te:
>>>>> Here it is in a nutshell:
>>>>>
>>>>>
>>>>>
>>>>> I have a field that needs to be set equal to the auto-increment ID as=
a
>>>record is entered. I don't know how to do this without a subsequent UPDA=
TE
>>>(which I can do with a trigger). Is there any way to avoid the cost of a=
n
>>>UPDATE?
>>>>>
>>>>>
>>>>>
>>>>> Here's a more concrete description of the problem:
>>>>>
>>>>>
>>>>>
>>>>> CREATE TABLE t (
>>>>>
>>>>> id INT(11) AUTO-INCREMENT PRIMARY,
>>>>>
>>>>> xxx INT(11)
>>>>>
>>>>> );
>>>>>
>>>>>
>>>>>
>>>>> When a record is added to table `t`, I need to set `xxx` to the value
>>>generated for `id`. (`xxx` might be changed later.)
>>>>>
>>>>>
>>>>>
>>>>> Is there anything clever I can do?
>>>>>
>>>>>
>>>>>
>>>>> Regards,
>>>>>
>>>>>
>>>>>
>>>>> Jerry Schwartz
>>>>>
>>>>> Global Information Incorporated
>>>>>
>>>>> 195 Farmington Ave.
>>>>>
>>>>> Farmington, CT 06032
>>>>>
>>>>>
>>>>>
>>>>> 860.674.8796 / FAX: 860.674.8341
>>>>>
>>>>> E-mail: =A0 jerry@gii.co.jp
>>>>>
>>>>> Web site: =A0 www.the-infoshop.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> =A0- michael dykman
>>>> =A0- mdykman@gmail.com
>>>>
>>>> =A0May the Source be with you.
>>>>
>>>
>>>
>>>
>>>--
>>> - 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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djerry@gii.co=
..jp
>>
>>
>>
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djerry@gii.co.=
jp
>
>
>
>
>



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

=A0May 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

RE: CURRENT insert ID

>-----Original Message-----
>From: João Cândido de Souza Neto [mailto:joao@consultorweb.cnt.br]
>Sent: Friday, January 21, 2011 12:47 PM
>To: mysql@lists.mysql.com
>Subject: Re: CURRENT insert ID
>
>Ok, you must have your own reasons to do that.
>
>The fact is: You can´t set the auto_incremente value field to another field
>in the same table and record even in a trigger.
>
>So, the best way is a second update.
>
[JS] That's what I'm trying to do now, but I'm using MS Access through an ODBC
connection and I haven't figured out how to retrieve last_insert_id.

I should ask in the myodbc forum.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>--
>João Cândido de Souza Neto
>
>"Darryle Steplight" escreveu na mensagem
>news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-GjM@mail.gma il.com...
>@Joao - I'm currently building a database out right now that has this
>scenario. One field can be the primary key, that has a purpose for holding
>the record id, another field can hold the value. Let say there are two
>fields, id, s_id. Initially, you insert a record and `id` is now 100 and you
>update s_id to be 100. But for whatever reason, later down the road you
>need s_id to be 200. You can just update the s_id field instead of deleting
>the entire record and inserting an entire new one with X amount of fields.
>Updating one field is a lot less work than deleting and inserting. I have my
>tables set up so I won't have to use the primary key for queries, I will
>only use the s_id field.
>
>2011/1/21 João Cândido de Souza Neto
>
>> I can´t think about how useful for you would be to have two fields with
>> the
>> same value.
>>
>> --
>> João Cândido de Souza Neto
>>
>> ""Jerry Schwartz"" escreveu na mensagem
>> news:007501cbb98a$177acba0$467062e0$@co.jp...
>> Here it is in a nutshell:
>>
>>
>>
>> I have a field that needs to be set equal to the auto-increment ID as a
>> record is entered. I don't know how to do this without a subsequent UPDATE
>> (which I can do with a trigger). Is there any way to avoid the cost of an
>> UPDATE?
>>
>>
>>
>> Here's a more concrete description of the problem:
>>
>>
>>
>> CREATE TABLE t (
>>
>> id INT(11) AUTO-INCREMENT PRIMARY,
>>
>> xxx INT(11)
>>
>> );
>>
>>
>>
>> When a record is added to table `t`, I need to set `xxx` to the value
>> generated for `id`. (`xxx` might be changed later.)
>>
>>
>>
>> Is there anything clever I can do?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> E-mail: jerry@gii.co.jp
>>
>> Web site: www.the-infoshop.com
>>
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=dsteplight@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=jerry@gii.co.jp





--
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: CURRENT insert ID

>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@gmail.com]
>Sent: Friday, January 21, 2011 1:27 PM
>To: Jerry Schwartz
>Cc: MySql
>Subject: Re: CURRENT insert ID
>
>You don't need to do an update:
>
>...
>
>new.xxx = new.id
>...
>
[JS] I wish it were that easy. new.id is null until after the INSERT has
completed:

SHOW CREATE TABLE xxx\G
*************************** 1. row ******************
Table: xxx
Create Table: CREATE TABLE `xxx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vv` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

SHOW CREATE TRIGGER foo\G
*************************** 1. row ***************************
Trigger: foo
sql_mode:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTI ON
SQL Original Statement: CREATE DEFINER=`access`@`%` TRIGGER foo BEFORE UPDATE
ON xxx
FOR EACH ROW
SET NEW.vv = NEW.id
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci

INSERT INTO xxx VALUES (NULL,NULL);
SELECT * FROM xxx;
+----+------+
| id | vv |
+----+------+
| 1 | NULL |
+----+------+
1 row in set (0.00 sec)

I'm tearing my hair out.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




--
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: CURRENT insert ID

Just an idear..

Don't auto_increment the main table.. create a unique Id table,
auto_increment that, and grab that value first for use with both fields
in your main table.

Donovan


--
D Brooke

--
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: CURRENT insert ID

--Apple-Mail-1--474874640
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

Hi,

On 22/01/2011, at 11:27 AM, Donovan Brooke wrote:

> Just an idear..
>=20
> Don't auto_increment the main table.. create a unique Id table, =
auto_increment that, and grab that value first for use with both fields =
in your main table.

This can be wrapped into a trigger, so the main table functions as =
usual:

CREATE TABLE _sequence (
Name varchar(20) NOT NULL PRIMARY KEY,
Value INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=3DInnoDB;

CREATE TABLE dupkey (
id INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY,
DupKey INT UNSIGNED NOT NULL DEFAULT 0,
Value VARCHAR(20) NOT NULL DEFAULT ''
) ENGINE=3DInnoDB;

INSERT INTO _sequence
VALUES ('dupkey', 0);

DELIMITER //
CREATE TRIGGER befins_dupkey BEFORE INSERT ON dupkey FOR EACH ROW
BEGIN
DECLARE v_id INT UNSIGNED;
=20
UPDATE _sequence SET Value =3D (LAST_INSERT_ID(Value+1)) where name =3D =
'dupkey';
SET NEW.id :=3D LAST_INSERT_ID(),
NEW.DupKey :=3D LAST_INSERT_ID();
END//
DELIMITER ;

INSERT INTO dupkey (Value)
VALUES ('test 1'), ('test 2');

SELECT * FROM dupkey;
+----+--------+--------+
| id | DupKey | Value |
+----+--------+--------+
| 1 | 1 | test 1 |
| 2 | 2 | test 2 |
+----+--------+--------+
2 rows in set (0.00 sec)

Cheers,
Jesper

>=20
> Donovan
>=20
>=20
> --=20
> D Brooke
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmysql@wisborg.dk=

>=20


--Apple-Mail-1--474874640--

Re: CURRENT insert ID

Seeing from later posts that you're using InnoDB, why don't you simply
wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE,
but I'm not sure I understand the need to mess w/ triggers.

BEGIN
INSERT INTO t(id) NULL
UPDATE t SET xxx=last_insert_id()
COMMIT

Best,

/ Carsten

Den 21-01-2011 17:41, Jerry Schwartz skrev:
> Here it is in a nutshell:
>
>
>
> I have a field that needs to be set equal to the auto-increment ID as a record is entered. I don’t know how to do this without a subsequent UPDATE (which I can do with a trigger). Is there any way to avoid the cost of an UPDATE?
>
>
>
> Here’s a more concrete description of the problem:
>
>
>
> CREATE TABLE t (
>
> id INT(11) AUTO-INCREMENT PRIMARY,
>
> xxx INT(11)
>
> );
>
>
>
> When a record is added to table `t`, I need to set `xxx` to the value generated for `id`. (`xxx` might be changed later.)
>
>
>
> Is there anything clever I can do?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
> E-mail: jerry@gii.co.jp
>
> Web site: www.the-infoshop.com
>
>
>
>

--
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: CURRENT insert ID

ehr...

Den 23-01-2011 15:36, Carsten Pedersen skrev:
> Seeing from later posts that you're using InnoDB, why don't you simply
> wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE,
> but I'm not sure I understand the need to mess w/ triggers.
>
> BEGIN
> INSERT INTO t(id) NULL
> UPDATE t SET xxx=last_insert_id()

UPDATE t SET xxx=i WHERE i=last_insert_id()

obviously.

Sorry.

Best,

/ Carsten



--
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: CURRENT insert ID

I'll have to investigate how to do a transaction from Access. I guess
pass-through queries might do it, but I'm not sure.

>-----Original Message-----
>From: Carsten Pedersen [mailto:carsten@bitbybit.dk]
>Sent: Sunday, January 23, 2011 9:36 AM
>To: Jerry Schwartz
>Cc: 'mysql.'
>Subject: Re: CURRENT insert ID
>
>Seeing from later posts that you're using InnoDB, why don't you simply
>wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE,
>but I'm not sure I understand the need to mess w/ triggers.
>
>BEGIN
>INSERT INTO t(id) NULL
>UPDATE t SET xxx=last_insert_id()
>COMMIT
>
[JS] I'll have to investigate how to do a transaction from Access. I guess
pass-through queries might do it, but I'm not sure.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>Best,
>
>/ Carsten
>
>Den 21-01-2011 17:41, Jerry Schwartz skrev:
>> Here it is in a nutshell:
>>
>>
>>
>> I have a field that needs to be set equal to the auto-increment ID as a
>record is entered. I don't know how to do this without a subsequent UPDATE
>(which I can do with a trigger). Is there any way to avoid the cost of an
>UPDATE?
>>
>>
>>
>> Here's a more concrete description of the problem:
>>
>>
>>
>> CREATE TABLE t (
>>
>> id INT(11) AUTO-INCREMENT PRIMARY,
>>
>> xxx INT(11)
>>
>> );
>>
>>
>>
>> When a record is added to table `t`, I need to set `xxx` to the value
>generated for `id`. (`xxx` might be changed later.)
>>
>>
>>
>> Is there anything clever I can do?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> E-mail: jerry@gii.co.jp
>>
>> Web site: www.the-infoshop.com
>>
>>
>>
>>




--
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: CURRENT insert ID

>-----Original Message-----
>From: Donovan Brooke [mailto:lists@euca.us]
>Sent: Friday, January 21, 2011 7:28 PM
>Cc: mysql@lists.mysql.com
>Subject: Re: CURRENT insert ID
>
>Just an idear..
>
>Don't auto_increment the main table.. create a unique Id table,
>auto_increment that, and grab that value first for use with both fields
>in your main table.
>
[JS] I've thought of that, but it creates another problem.

Let's say I add a record to the ID table, thereby auto-incrementing its key.
Now I need to retrieve that key value. How do I do that while retaining some
semblance of data integrity? I'd have to do something like "SELECT MAX()",
which fails to retrieve "my" value if someone else has inserted a record in
the meantime.

I don't, from Access, have the ability to throw a lock on the table (so far as
I know). I guess maybe I could do that with pass-through queries, but I'm not
sure.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com




>Donovan
>
>
>--
>D Brooke
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
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: CURRENT insert ID

On 24/01/2011 15:42, Jerry Schwartz wrote:
>> -----Original Message-----
>> From: Donovan Brooke [mailto:lists@euca.us]
>> Sent: Friday, January 21, 2011 7:28 PM
>> Cc: mysql@lists.mysql.com
>> Subject: Re: CURRENT insert ID
>>
>> Just an idear..
>>
>> Don't auto_increment the main table.. create a unique Id table,
>> auto_increment that, and grab that value first for use with both fields
>> in your main table.
>>
> [JS] I've thought of that, but it creates another problem.
>
> Let's say I add a record to the ID table, thereby auto-incrementing its key.
> Now I need to retrieve that key value. How do I do that while retaining some
> semblance of data integrity? I'd have to do something like "SELECT MAX()",
> which fails to retrieve "my" value if someone else has inserted a record in
> the meantime.

That's what LAST_INSERT_ID() is for:

http://dev.mysql.com/doc/refman/5.0/en/information-functions .html#function_last-insert-id

This is on a per-connection basis, so even if another connection inserts
a line in the meantime your query will return the auto-increment value
of the line you inserted.

Most programming languages with an interface to MySQL, either built-in
or via a module, implement this natively. For example, in PHP:

mysql_query("insert into mytable set name = 'foo'");
$id = mysql_insert_id();

the value of $id will be the auto-increment number from the line you
just inserted.

Mark
--
http://mark.goodge.co.uk
http://www.ratemysupermarket.com

--
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: CURRENT insert ID

2011/1/21 Jerry Schwartz :
>>-----Original Message-----
>>From: João Cândido de Souza Neto [mailto:joao@consultorweb.cnt.=
br]
>>Sent: Friday, January 21, 2011 12:47 PM
>>To: mysql@lists.mysql.com
>>Subject: Re: CURRENT insert ID
>>
>>Ok, you must have your own reasons to do that.
>>
>>The fact is: You can´t set the auto_incremente value field to anothe=
r field
>>in the same table and record even in a trigger.
>>
>>So, the best way is a second update.
>>
> [JS] That's what I'm trying to do now, but I'm using MS Access through an=
ODBC
> connection and I haven't figured out how to retrieve last_insert_id.

I will tell you a secret. But shhhhhhhhhh. Do not tell anyone:

------8<------8<------8<------8<------8<------8<------8<------8<------8<---=
---8<------8<------
mysql> create table mytable(id int auto_increment primary key, name
varchar(255));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into mytable (name) values ('test data');
Query OK, 1 row affected (0.00 sec)

mysql> select id from mytable where id is null; -- OMG!!!
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
------8<------8<------8<------8<------8<------8<------8<------8<------8<---=
---8<------8<------


--=20
Jaime Crespo
MySQL & Java Instructor
Software Developer
Warp Networks


--
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