0

I have a table holding items with item_order column as non-nullable INT.

When I create a new item I need the highest item_order existing in the table, so I can increment it and insert new record. Below an abstract example of what I mean:

INSERT INTO item (name, item_order)
VALUES ('New Item', (SELECT MAX(item_order) FROM item AS item_order) + 1)

I assume this is crazy talk :) How do I achieve what I need?

UPDATE:

I found this useful: Increment a database field by 1

Can I then use SELECT MAX(item_order) + 1 as I showed above, as VALUES argument?

Community
  • 1
  • 1
lesssugar
  • 15,486
  • 18
  • 65
  • 115

1 Answers1

0

change item_order to an AUTO_INCREMENT column.

ALTER TABLE `item` 
CHANGE COLUMN `item_order` `item_order` INT(11) NOT NULL AUTO_INCREMENT;

This assumes that you've made it your primary key, otherwise if you really want to do it that way then you can do.

SET @nextItemOrder = IFNULL((SELECT MAX(item_order) FROM item AS item_order),0) + 1;
INSERT INTO item (name, item_order)
VALUES ('New Item', @nextItemOrder);
Jonathan
  • 2,778
  • 13
  • 23