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

Answer 2

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

Correction is just adding "F1" table alias.

This standard sql solution works fine on various dbms (not only mysql)

---

I also suggest an improvement over:

• SELECT COUNT(*) +1 (Problem: if some row gets deleted you may collide with existing index)

• SELECT MAX(featured_order)+1 (Problem: the first insert with empty table gets error)

SELECT (COALESCE(MAX(featured_order), 0)+1) (no Problem)

Answer 3

Use a trigger:

drop trigger if exists featured_insert_trigger; 

delimiter //
create trigger featured_insert_trigger before insert on featured
for each row
begin
  set new.featured_order = ifnull((select max(featured_order) from featured), -1) + 1;
end; //
delimiter ;

Now your inserts look like this:

insert into featured (entry_id) values (200);

featured_order will be set to the highest featured_order value plus one. This caters for rows being deleted/updated and always guarantee uniqueness.

The ifnull is there in case there are no rows in the table, in which case the first value will be zero.

This code has been tested as works correctly.

Answer 4

You have to simpley use alias that will solve the problem :

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

Answer 5

From the MySQL manual regarding subqueries:

Another restriction is that currently you cannot modify a table and select from the same table in a subquery.

Perhaps an alias or a join (otherwise useless) in the subquery would help here.

EDIT: It turns out that there's a work-around. The work-around is described http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/.