homepage Welcome to WebmasterWorld Guest from 54.196.201.253
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Insert unique row number with auto increment.
BarryStCyr




msg:4123990
 9:28 pm on Apr 28, 2010 (gmt 0)

The following statement gives me a MYSQL error of #1093 - You can't specify target table 'fests' for update in FROM clause

INSERT ftable(

fid
)
VALUES (
(

SELECT fid +1
FROM `ftable`
WHERE ftype = "ren"
ORDER BY fid DESC
LIMIT 1
)
)


How can I accomplish this as one statement to avoid a race condition.

 

brotherhood of LAN




msg:4123995
 9:33 pm on Apr 28, 2010 (gmt 0)

User defined variables [dev.mysql.com] may be useful to you, though it'll be two queries.

SET @myvar= (SELECT fid +1
FROM `ftable`
WHERE ftype = "ren"
ORDER BY fid DESC
LIMIT 1 );


INSERT ftable(fid)
VALUES (
(@myvar)


Also you may want to consider changing your query to use MAX(fid)+1 as I'm fairly sure it'd be more efficient; i.e. no sorting of all matching rows and then limiting to 1.

INSERT ftable(fid)
VALUES ('')


This should also produce the desired result if 'fid' is an auto increment field.

BarryStCyr




msg:4124015
 9:59 pm on Apr 28, 2010 (gmt 0)

INSERT ftable(fid)
VALUES ('')


would probably work, but the value if max(fid) depends on the value of ftype. There are at least two values for ftype so an auto-increment would not work.

Would
SET @myvar= (SELECT fid +1
FROM `ftable`
WHERE ftype = "ren"
ORDER BY fid DESC
LIMIT 1 );


and

INSERT ftable(fid)
VALUES (
(@myvar)


be executed as a single query and would that prevent another user from grabbing the same number before the insertion?

brotherhood of LAN




msg:4124018
 10:07 pm on Apr 28, 2010 (gmt 0)

max(fid) depends on the value of ftype


My mistake.

RE: your original query, I found renaming one of the tables to an alias makes your query fine.

INSERT ftable(

fid
)
VALUES (
(

SELECT fid +1
FROM `ftable` AS x
WHERE ftype = "ren"
ORDER BY fid DESC
LIMIT 1
)
)

coopster




msg:4124321
 1:30 pm on Apr 29, 2010 (gmt 0)

I realize I am not seeing all the details here but it looks as if you are attempting to use a compound key and would like to auto-increment the unique value within a group. This can indeed be accomplished, but only if the table is of type MyISAM.

Using AUTO_INCREMENT [dev.mysql.com]

BarryStCyr




msg:4124836
 6:44 am on Apr 30, 2010 (gmt 0)

Thanks coopster. That solution works perfectly.

BofL, I tried your solution, but still get the error about the target table. Thanks for the help, anyway.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved