0

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

Shadow
  • 33,525
  • 10
  • 51
  • 64
Sujith
  • 17
  • 11
  • 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 Sep 02 '16 at 05:23
  • Possible duplicate of [Select from same table as an Insert or Update](http://stackoverflow.com/questions/205190/select-from-same-table-as-an-insert-or-update) – Shadow Sep 02 '16 at 07:40

3 Answers3

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.

Ponnarasu
  • 635
  • 1
  • 11
  • 24
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
Margu
  • 175
  • 3
  • 14
  • Glad it works, so please accept the answer so others can see this question is solved :) – Margu Sep 05 '16 at 05:36
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;
Manchikanti Aishwarya
  • 290
  • 1
  • 7