mysql insert query with mix of static value and inner select query

Question

I have a table called productcategory I want to insert two static values to the columns category_name and parent_id and need to insert column sort_order with a value generated from inner select query

INSERT INTO productcategory(category_name,parent_id,sort_order) 
values('Flowers',0,(select MAX(sort_order)+1 from productcategory where parent_id=0))

SQL query not working
Thanks in Advance

Is the actual error message classified? I cannot think of any other reason for not sharing it with us. My guess is that you cannot select from the table in a subquery which you try to modify. — Shadow
– Shadow, Commented Sep 2, 2016 at 5:23
Possible duplicate of Select from same table as an Insert or Update — Shadow
– Shadow, Commented Sep 2, 2016 at 7:40

Margu · Accepted Answer · 2016-09-02 05:54:07Z

1

It should work like this:

INSERT INTO productcategory(category_name,parent_id,sort_order)
SELECT 'Flowers', 0, MAX(sort_order)+1
FROM   productcategory
WHERE  parent_id = 0

answered Sep 2, 2016 at 5:54

Margu

1753 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Margu Over a year ago

Glad it works, so please accept the answer so others can see this question is solved :)

Ponnarasu · Accepted Answer · 2016-09-02 05:57:48Z

1

You can't select from the table in a sub query which you try to update or insert.

As per my knowledge this can be achieved by using SET method like below.

SELECT @maxSortOrder := IFNULL(MAX(sort_order),0)+1 FROM productcategory WHERE parent_id=0;
INSERT INTO productcategory(category_name,parent_id,sort_order) VALUES('Flowers',0,@maxSortOrder);

This may help you.

edited Sep 2, 2016 at 5:57

answered Sep 2, 2016 at 5:31

Ponnarasu

6351 gold badge12 silver badges26 bronze badges

Comments

Manchikanti Aishwarya · Accepted Answer · 2016-09-02 05:40:47Z

0

Now try this:

CREATE TABLE dupproduct AS (SELECT * FROM productcategory);

INSERT INTO productcategory (
  category_name,
  parent_id,
  sort_order
) 
VALUES
  (
    'Flowers',
    0,
    (SELECT 
      MAX(sort_order) + 1 
    FROM
      dupproduct 
    WHERE parent_id = 0)
  );


  DROP TABLE dupproduct;

edited Sep 2, 2016 at 5:40

answered Sep 2, 2016 at 5:23

Manchikanti Aishwarya

2901 silver badge7 bronze badges

1 Comment

Shadow Over a year ago

Do you seriously suggest to copy the entire table to make the query work?

Collectives™ on Stack Overflow

mysql insert query with mix of static value and inner select query

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related