Possible to copy the key field to another on INSERT?
When creating a record, the first field (KeyField)... KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT ...is it possible to copy this auto-generated value into another field when using the same INSERT that creates the record? Or would I have to use an UPDATE query using LAST_INSERT_ID() immediately after the INSERT statement? Thanks for any ideas you have. Start Here to Find It Fast! - http://www.US-Webmasters.com/best-start-page/ $8.77 Domain Names - http://domains.us-webmasters.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Possible to copy the key field to another on INSERT?
select LAST_INSERT_ID() ; Under high concurrency , it would return other threads value to you. On Wed, Oct 17, 2012 at 2:23 PM, W. D. w...@us-webmasters.com wrote: When creating a record, the first field (KeyField)... KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT ...is it possible to copy this auto-generated value into another field when using the same INSERT that creates the record? Or would I have to use an UPDATE query using LAST_INSERT_ID() immediately after the INSERT statement? Thanks for any ideas you have. Start Here to Find It Fast!™ - http://www.US-Webmasters.com/best-start-page/ $8.77 Domain Names - http://domains.us-webmasters.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- B.rgds / Whitepoplar (杨德华）
Re: Possible to copy the key field to another on INSERT?
Take a look at TRIGGERS C. PS: I am curious to know why you would do that anyway 2012/10/17 W. D. w...@us-webmasters.com When creating a record, the first field (KeyField)... KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT ...is it possible to copy this auto-generated value into another field when using the same INSERT that creates the record? Or would I have to use an UPDATE query using LAST_INSERT_ID() immediately after the INSERT statement? Thanks for any ideas you have. Start Here to Find It Fast!™ - http://www.US-Webmasters.com/best-start-page/ $8.77 Domain Names - http://domains.us-webmasters.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: Possible to copy the key field to another on INSERT?
Hello Dehua, On 10/17/2012 3:33 AM, Dehua Yang wrote: select LAST_INSERT_ID() ; Under high concurrency , it would return other threads value to you. Incorrect. The results of LAST_INSERT_ID() are connection-specific. The activity on other connections will not change or alter the value for the current connection. Only actions taken by the current connection can change this value. http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Possible to copy the key field to another on INSERT?
Hello Shawn Thanks for your tips. We send the select LAST_INSERT_ID() by our data middle ware. I'll ask the middle ware team to check it out. On Wed, Oct 17, 2012 at 9:33 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Hello Dehua, On 10/17/2012 3:33 AM, Dehua Yang wrote: select LAST_INSERT_ID() ; Under high concurrency , it would return other threads value to you. Incorrect. The results of LAST_INSERT_ID() are connection-specific. The activity on other connections will not change or alter the value for the current connection. Only actions taken by the current connection can change this value. http://dev.mysql.com/doc/**refman/5.5/en/information-** functions.html#function_last-**insert-idhttp://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- B.rgds / Whitepoplar (杨德华）
Re: Possible to copy the key field to another on INSERT?
At 02:44 10/17/2012, Claudio Nanni, wrote: Take a look at TRIGGERS C. PS: I am curious to know why you would do that anyway Will want this 'AssociatedWith' field to be associated with an older records' KeyField so I can search for a group of records by this field. Start Here to Find It Fast! - http://www.US-Webmasters.com/best-start-page/ $8.99 Domain Names - http://domains.us-webmasters.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Possible to copy the key field to another on INSERT?
At 02:44 10/17/2012, Claudio Nanni wrote: Take a look at TRIGGERS C. Thanks Claudio. I wrote a trigger that MySQL accepted. However, when I tried to insert a new record: Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger I Googled the error: http://dev.mysql.com/doc/refman/5.5/en/faqs-triggers.html#qandaitem-B-5-1-9 B.5.9: Can triggers access tables? A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger. From this it seems that a trigger will not allow me to auto-populate the `AssociatedWith` field the value of the `KeyField`. It looks like I will have to populate that field immediately after creating the record with an additional statement, yes? Does anyone have any other ideas on how to populate the value of one field with the value of the `KeyField' on creation(INSERT)? I want to be able to search on the `AssociatedWith` field to find all records that rely on that first record's `KeyField`. PS: I am curious to know why you would do that anyway 2012/10/17 W. D. w...@us-webmasters.com When creating a record, the first field (KeyField)... KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT ...is it possible to copy this auto-generated value into another field when using the same INSERT that creates the record? Or would I have to use an UPDATE query using LAST_INSERT_ID() immediately after the INSERT statement? Thanks for any ideas you have. Start Here to Find It Fast! - http://www.US-Webmasters.com/best-start-page/ $9.99 Domain Names - http://domains.us-webmasters.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
