MySQL INSERT Using Subquery with COUNT() on the Same Table

Question

I'm having trouble getting an INSERT query to execute properly, and I can't seem to find anything on Google or Stack Overflow that solves this particular issue.

I'm trying to create a simple table for featured entries, where the entry_id is saved to the table along with it's current order.

My desired output is this:

If the featured table currently has these three entries:

featured_id    entry_id    featured_order
1              27          0
2              54          1
4              23          2

I want the next entry to save with featured_order=3.

I'm trying to get the following query to work with no luck:

INSERT INTO `featured`
(
    `entry_id`, `featured_order`
)
VALUES
(
    200,
    (SELECT COUNT(*) AS `the_count` FROM `featured`)
)

The error I'm getting is: You can't specify target table 'featured' for update in FROM clause.

Can anyone help with a solution that gets the count without causing an error?

Thanks in advance!

Answer 1

Here is a cool thing: MySQL's INSERT . . . SELECT:

INSERT INTO `featured`
(
    `entry_id`, `featured_order`
)
SELECT 200, COUNT(*) + 1
FROM `featured`

No subquery required.

---

@Bohemian has a good point:

Better to use max(featured_order) + 1 if you use this approach

So a better query would probably be:

INSERT INTO `featured`
(
    `entry_id`, `featured_order`
)
SELECT 200, MAX(`featured_order`) + 1
FROM `featured`

His trigger method describe in his answer is also a good way to accomplish what you want.

---

The potential problem with query 1 is if you ever delete a row the rank will be thrown off, and you'll have a duplicate in featured_order. With the second query this is not a problem, but you will have gaps, just as if you were using an auto-increment column.

If you absolutely must have an order with no gaps the best solution I know of is to run this series of queries:

SET @pos:=0;

DROP TABLE IF EXISTS temp1;

CREATE TEMPORARY TABLE temp1 LIKE featured;

ALTER TABLE featured ORDER BY featured_order ASC;

INSERT INTO temp1 (featured_id, entry_id, featured_order) 
SELECT featured_id, entry_id, @pos:=@pos+1 FROM words;

UPDATE featured 
JOIN temp1 ON featured.featured_id = temp1.featured_id 
SET featured.rank = temp1.rank;

DROP TABLE temp1;

Whenever you delete a row