-1

I want to increment the order of every video by one every time a video gets inserted so that the new video takes its place

UPDATE services SET orders = orders + 1

This doesn't work, I get this error, is there an easy fix? I could fix it in PHP but I'll have to fix it in multiple places and find the buggy code, it's just one-liner code, so much easier if it works as a trigger.

4
  • Missing NEW. and/or OLD.?
    – Rick James
    Nov 11, 2019 at 20:04
  • @RickJames Yes, This is the correct answer, for each doesn't work in mysql 5.7
    – Lynob
    Nov 12, 2019 at 8:03
  • When you have troubles with a TRIGGER, back off and use, say, a Stored Procedure. Triggers can't do everything.
    – Rick James
    Nov 12, 2019 at 18:43
  • @RickJames you are so correct, I backed off when I started having troubles with this trigger.
    – Lynob
    Nov 12, 2019 at 21:03

1 Answer 1

0

You forget that trigger works with ONE record.

You may, of course, do something like

CREATE TRIGGER increase_orders
ON services
BEFORE INSERT 
FOR EACH ROW
SET NEW.orders = ( SELECT 1 + MAX(services.orders)
                   FROM services
                   WHERE services.video_id = NEW.video_id );

But when 2 clients would insert a record with the same video_id you may obtain duplicated orders value. And you can do close to nothing with that problem while using this method.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.