Welcome to WebmasterWorld Guest from 23.20.12.34

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:4843
votes: 2


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:4843
votes: 2


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:12541
votes: 1


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.