CODEWITHSUNDEEP
mysqlsql
Yamaha32088
Yamaha32088

Reputation: 4163

Find highest value in a column matching given statement increase by 1

I am trying to write an SQL query that finds a value in a column and increases it by one. For example:

 id | reply                          | comment_parent_id | order_by 
----+--------------------------------+-------------------+----------
  1 | Hello World I am a reply       |                 1 |        1 
  2 | Hello World I am a reply also  |                 1 |        2 
  3 | Hello World I am a reply also  |                 1 |        3 
  5 | Hello World I am a reply also  |                 2 |        1

The above is an example of my table. When I insert a new row and the comment_parent_id = 1 then that row being inserted the order_by field needs to be 4. So if I inserted another new row and the comment_parent_id = 2 then the order_by field needs to equal 2. I hope I explained my self clear enough. Thanks for any help.

Upvotes: 0

Views: 50

Answers (2)

Barmar
Barmar

Reputation: 781974

INSERT INTO table (reply, comment_parent_id, order_by)
SELECT @new_reply, @new_parent_id, IFNULL(MAX(order_by)+1, 1)
FROM table
WHERE comment_parent_id = @new_parent_id

Upvotes: 1

Sean
Sean

Reputation: 12433

Try a nested SELECT in your INSERT -

INSERT INTO test
     SELECT NULL, 
            'Hello World I am a reply also', 
            ?, 
            count(comment_parent_id)+1 
     FROM test 
     WHERE comment_parent_id = ?;

The ?'s represent the comment_parent_id that you are inserting.

see this SQLFiddle example - http://sqlfiddle.com/#!2/d230f/1

Upvotes: 0

Related Questions