This is a simple piece of code which describing how to update special order field in table. For example, you have table ‘photos’:
+ --------------------------------- + | ID | UserID | OrderID | ImgURL | + --------------------------------- + | 1132 | 1 | 0 | ... | | 2124 | 1 | 0 | ... | | 3456 | 1 | 0 | ... | ..................................... | N | 1 | 0 | ... | + --------------------------------- +
OrderID field value by default is 0. If some images has been deleted order lose its consistency.
To repair order we can use single transaction:
BEGIN; SET @ORDER := 0; UPDATE photos SET order_id = (SELECT @ORDER := @ORDER + 1) WHERE user_id = 1; COMMIT;
After executing this on table ‘images’ we`ll get such result:
+ --------------------------------- + | ID | UserID | OrderID | ImgURL | + --------------------------------- + | 1132 | 1 | 1 | ... | | 2124 | 1 | 2 | ... | | 3456 | 1 | 3 | ... | ..................................... | N | 1 | M | ... | + --------------------------------- +
Some developers instead of using resources of mysql script language trying to solve this problem by executing numerous call, such ‘UPDATE table SET order_id = [value] WHERE id = [id] LIMIT 1;’. It`s wrong. MySQL language have a lot of ways to make it easy. At least UDF`s.
