All,

I'm trying to write a PHP function that creates a new unique ID by simply incrementing the current largest (highest) one in a column. I have a column ID in the table SPECIMEN, with ID being an INTEGER, unique, and a primary key. Basically, all I want the SQL query to do is return the "ID" value of the row that has the highest "ID" value... my function will then add one and do the insert. I'm doing this instead of AUTOINCREMENT because of portability issues. Here's my query that's failing:

SELECT id FROM specimen WHERE ID=(SELECT MAX(ID) FROM specimen)

mySQL is giving me an error, saying there's a syntax error... when I run "SELECT MAX(ID) FROM specimen" I get "18", but when I try to do a subquery using it as the ID argument, it fails... does mySQL simply not support this??

Thanks!

p.s. -- I'm running this in the mySQL command line...

    Is there a reason you aren't using an auto-increment field?

    There are lots of problems you can code yourself into involving parallel access that won't show up until it's too late (i.e. the system is in production and data is getting corrupted) when you try to "roll your own" if you aren't sure what you're doing.

      Originally posted by Sxooter
      Is there a reason you aren't using an auto-increment field?

      There are lots of problems you can code yourself into involving parallel access that won't show up until it's too late (i.e. the system is in production and data is getting corrupted) when you try to "roll your own" if you aren't sure what you're doing.

      Yeah, I'm realizing that slowly... I'm more worried about the app getting up and runnin than trying to worry about portability to pgSQL or anything...

      Is it true that mySQL doesn't support ANY subquerying?? When is support for subq projected for?

        MySQL doesn't support sub queries because they weren't important enough to the authors and users until recently to start working on them I guess.

        Autoincrement fields exist in almost every database engine, although, since SQL92 doesn't define them, they are all a little different. but it's better to figure out how to do them in each database than to try and come up with a code solution that probably will never be reliable in a highly parallel environment.

          By the way, wouldn't "select max(id) from specimen" work just as well as your subselect?

          Note that inside a transaction block (using innodb tables) you could make this work, but you would need to lock the whole table while doing it.

            Try this,

            select max(id) from specimen;

            then put the result in a variable

            $max_id = $row[0]; //somethin on those lines

            $max_id += 1;

            //then have another select where you insert the new id

            insert into specimen (id,col_name, col_name2...) Values ('max_id',...)

            somethin along thos lines is what i had to do.

            hope it helps

              Write a Reply...