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:cars...@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: je...@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:mailto:je...@gii.co.jp je...@gii.co.jp Web site:http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: Donovan Brooke [mailto:li...@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: je...@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=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
On 24/01/2011 15:42, Jerry Schwartz wrote: -Original Message- From: Donovan Brooke [mailto:li...@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=arch...@jab.org
Re: CURRENT insert ID
2011/1/21 Jerry Schwartz je...@gii.co.jp:
-Original Message-
From: João Cândido de Souza Neto [mailto:j...@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 will tell you a secret. But shh. 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--
--
Jaime Crespo
MySQL Java Instructor
Software Developer
Warp Networks
http://warp.es
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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:mailto:je...@gii.co.jp je...@gii.co.jp Web site:http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.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=arch...@jab.org
CURRENT insert ID
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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com
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. - 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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@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=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: Michael Dykman [mailto:mdyk...@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: je...@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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@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=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: Jerry Schwartz [mailto:je...@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:mdyk...@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: je...@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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@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=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
I made a typo in my previous message. -Original Message- From: Jerry Schwartz [mailto:je...@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:je...@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:mdyk...@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: je...@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=arch...@jab.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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: CURRENT insert ID
@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 j...@consultorweb.cnt.br 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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com -- -- May the Source be with you.
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 dstepli...@gmail.com escreveu na mensagem news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-...@mail.gmail.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 j...@consultorweb.cnt.br 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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@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=arch...@jab.org
Re: CURRENT insert ID
You don't need to do an update: ... new.xxx = new.id ... On Fri, Jan 21, 2011 at 12:20 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Jerry Schwartz [mailto:je...@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:mdyk...@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: je...@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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@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=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- - michael dykman - mdyk...@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=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: João Cândido de Souza Neto [mailto:j...@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: je...@gii.co.jp Web site: www.the-infoshop.com -- João Cândido de Souza Neto Darryle Steplight dstepli...@gmail.com escreveu na mensagem news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-...@mail.gmail.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 j...@consultorweb.cnt.br 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 je...@gii.co.jp 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: mailto:je...@gii.co.jp je...@gii.co.jp Web site: http://www.the-infoshop.com/ www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@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=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: CURRENT insert ID
-Original Message- From: Michael Dykman [mailto:mdyk...@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_SUBSTITUTION 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: je...@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=arch...@jab.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=arch...@jab.org
Re: CURRENT insert ID
Hi,
On 22/01/2011, at 11:27 AM, Donovan Brooke wrote:
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.
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=InnoDB;
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=InnoDB;
INSERT INTO _sequence
VALUES ('dupkey', 0);
DELIMITER //
CREATE TRIGGER befins_dupkey BEFORE INSERT ON dupkey FOR EACH ROW
BEGIN
DECLARE v_id INT UNSIGNED;
UPDATE _sequence SET Value = (LAST_INSERT_ID(Value+1)) where name = 'dupkey';
SET NEW.id := LAST_INSERT_ID(),
NEW.DupKey := 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
Donovan
--
D Brooke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@wisborg.dk
