Geek Answers Handbook

MySQL INSERT Using a subquery with COUNT () in the same table

I'm having trouble getting a request INSERTto execute correctly, and I can't find anything on Google or Stack Overflow that solves this problem.

I am trying to create a simple table for recognized records, where it is entry_idstored in the table along with the current order.

My desired result:

If the table featuredcurrently has three entries:

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

I want the next record to be saved with featured_order= 3.

I am trying to get the following request to work with no luck:

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

The error that I get: You can't specify target table 'featured' for update in FROM clause.

Can someone help with a solution that gets the bill without causing an error?

Thanks in advance!

+5
5

: MySQL INSERT . . . SELECT:

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

.

@Bohemian :

max (featured_order) + 1,

, :

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

, , , .

1 , , , featured_order. , , .

, , , :

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;

,

+13

:

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 ;

:

insert into featured (entry_id) values (200);

featured_order featured_order . / .

ifnull , , .

.

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

F1. ( )

multi db, sintax mysql + oracle + db2

:

  • SELECT COUNT (*) +1 (: , )

  • SELECT MAX (featured_order) +1 (: )

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

+2

, :

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

MySQL :

, .

, ( ) .

EDIT: Turns out there is a workaround. The workaround is described at http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/ .

-1
source

More articles:


All Articles