Welcome to WebmasterWorld Guest from 54.205.96.97

Forum Moderators: open

Insert unique row number with auto increment.

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

5+ Year Member



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)

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



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)

5+ Year Member



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)

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



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)

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



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)

5+ Year Member



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.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month