1

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.

Improve this question
0

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Looks about right, but I got this error: #1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
@JackieAD . . . Your column is not using the default collation. Try changing @prev := '' to @prev := '' collate utf8_unicode_ci.
0

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')
Improve this answer

5 Comments

Am I understanding correctly that this would only be useful for future inserts and not for fixing the order values in the data currently inside of the table?
@JackieAD - Yes, the above solutions would prevent your data from ever getting in a state that needs updating. Assuming you could start with a new, empty table, this would be your ideal solution. Or you could run your update one time and switch to this in the future to prevent having to update again.
But what update operation? That's what I'm looking for a solution to still.
Ideally, I would have known I needed an order column from the start, but the data is already in there and needs to be updated.
Ok, even so, you should add this functionality so you won't need to update again in the future.
0

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...

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.