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.

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?

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
)
)

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]

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.