Welcome to WebmasterWorld Guest from 54.204.172.174

Forum Moderators: open

Message Too Old, No Replies

Insert unique row number with auto increment.

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

Junior Member

10+ Year Member

joined:June 2, 2005
posts:112
votes: 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.
9:33 pm on Apr 28, 2010 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4847
votes: 6


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.
9:59 pm on Apr 28, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:June 2, 2005
posts:112
votes: 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?
10:07 pm on Apr 28, 2010 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4847
votes: 6


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
)
)
1:30 pm on Apr 29, 2010 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12547
votes: 2


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]
6:44 am on Apr 30, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:June 2, 2005
posts:112
votes: 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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members