CODEWITHSUNDEEP
mysql
Jackie AD
Jackie AD

Reputation: 11

Update of MySQL table column to sequential digit based on another column

The current table looks something like this:

[id | section | order | thing]
[1  | fruits  | 0     | apple]
[2  | fruits  | 0     | banana]
[3  | fruits  | 0     | avocado]
[4  | veggies | 0     | tomato]
[5  | veggies | 0     | potato]
[6  | veggies | 0     | spinach]

I'm wondering how to make the table look more like this:

[id | section | order | thing]
[1  | fruits  | 1     | apple]
[2  | fruits  | 2     | banana]
[3  | fruits  | 3     | avocado]
[4  | veggies | 1     | tomato]
[5  | veggies | 2     | potato]
[6  | veggies | 3     | spinach]

"order" column updated to a sequential number, starting at 1, based on "section" column and "id" column.

Upvotes: 1

Views: 337

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can do this with an update by using a join. The second table to the join calculates the ordering, which is then used for the update:

update t join
       (select t.*, @rn := if(@prev = t.section, @rn + 1, 1) as rn
        from t cross join (select @rn := 0, @prev := '') const
       ) tsum
       on t.id = tsum.id
    set t.ordering = tsum.rn

Upvotes: 1

Glenn
Glenn

Reputation: 9160

Rather than storing the ordering, you could derive it:

SELECT t.id
      ,t.section
      ,@row_num := IF (@prev_section = t.section, @row_num+1, 1) AS ordering
      ,t.thing
      ,@prev_section := t.section
  FROM myTable t
      ,(SELECT @row_num := 1) x
      ,(SELECT @prev_value := '') y
  ORDER BY t.section, t.id

Note that order is a keyword and is therefore not the greatest for a column name. You could quote the column name or give it a different name...

Upvotes: 0

Steven Moseley
Steven Moseley

Reputation: 16345

You don't want to do this as an UPDATE, as that will be really slow.

Instead, do this on INSERT. Here's a simple one-line INSERT that will grab the next order number and inserts a record called 'kiwi' in the section 'fruits'.

INSERT INTO `table_name` (`section`, `order`, `thing`)
SELECT 'fruits', MAX(`order`) + 1, 'kiwi'
FROM `table_name` 
WHERE `section` = `fruits`

EDIT: You could also do this using an insert trigger, e.g.:

DELIMITER $$
CREATE TRIGGER `trigger_name` 
BEFORE INSERT ON `table_name`
FOR EACH ROW
BEGIN
    SET NEW.`order` = (SELECT MAX(`order`) + 1 FROM `table_name` WHERE `section` = NEW.`section`);
END$$
DELIMITER ;

Then you could just insert your records as usual, and they will auto-update the order value.

INSERT INTO `table_name` (`section`, `thing`)
VALUES ('fruits', 'kiwi')

Upvotes: 0

Related Questions