Hi everyone, I'm having a conceptual problem with managing autoincrements and I was hoping that the list could point me in the right direction ;-)
I've migrated a FMPro database which turned out to be a nightmare for various reasons, including the use of multivalue fields in FMPro that didn't export well. However, the real problem that I have is that FMPro allows you to use multiple autoincrement fields in the same table. The mysql server I've migrated to is at version 4.0.23. The autoincrement fields were exported as text and are unfortunately not sequential (in other words, there are gaps) due to data entry errors and a certain lack of rigor in the use of the previous FMPro db. The data in this field serves as a foreign key for another table, thus enabling groups of data to be created in the other table that all reference a same, and in theory, unique, id. To help with understanding, I'll give a sample below : Table1 : field1 int unsigned zerofill auto_increment primary key field2 varchar field3 date field4 int unsigned zerofill (references second table) Table2 : field1 int unsigned zerofill auto_increment primary key field2 int unsigned zerofill (corresponds to the values that need to appear in field4 of Table1) I have populated Table2.field2 with the data that was migrated from the original FMPro table. This has given me a list something like the following : +-------------+--------------+ | field1 | field2 | +-------------+--------------+ | 0000000001 | 0000000007 | | 0000000002 | 0000000009 | | 0000000003 | 0000000010 | | 0000000004 | 0000000012 | | 0000000005 | 0000000013 | | 0000000006 | 0000000014 | | 0000000007 | 0000000016 | | 0000000008 | 0000000017 | | 0000000009 | 0000000019 | | 0000000010 | 0000000022 | | 0000000011 | 0000000024 | | 0000000012 | 0000000026 | | 0000000013 | 0000000028 | | 0000000014 | 0000000030 | | 0000000015 | 0000000031 | +-------------+--------------+ As you can see, the values in field2 are not sequential. Unfortunately, I can not regenerate a new listing, since this would require modifying by hand all of the files in the office that have changed. So the first condition is to maintain these values. Now, I need to be able to continue generating values for field2 in a sequential manner i.e. field2 +1, each time a new group reference is created. This is where I am stuck. I have tried using last-insert_id() to help me with adding one to field2 of the last generated record, but this functionality is session and user specific, which means that when the user disconnects from the db and reconnects later, the initial reference count for last_insert_id is reset to zero I also tried changing my autoincrement value in field1 to start from a value that would be one more than the last value of field2 actually present in Table2. This works fine, but I don't know which SQL command to enter to get it to copy the value from the autoinc generation into field2 at the same time as it creates the autoinc value. To top it all, data entry is achieved through a form in OpenOffice.org via MyODBC, so any proposed solutions would have to be ODBC compliant. The main form corresponds to Table1, and links to the value of field2 in Table2 via a drop down list that executes a select SQL statement on the list of values present in field2. That way, the user can choose the value they want. I'm using a button activated macro to try and generate a new reference, but I'm failing with the necessarySQL command. Any help most greatly appreciated, TIA, Alex Thurgood -- Thurgood Alex <[EMAIL PROTECTED]> Cabinet Michel Richebourg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
