Mysql increment in loop by variable

Question

I am trying to use a simple MySQL query to update my table with positions. Let's assume I have a book table with an ID, a writer_id and a position field.

I want to be able to have positions from 1 ... x , but per writer_id. If I delete a record there will be a gap in my positions so this is why I want a simple query to reset all the positions without gaps.

Currently I have the following code (which works), but I think this should be possible a lot easier (and probably faster).

set @position := 0;
set @lastDependency := 0;
set @previousDependency := -1;

UPDATE `book` SET 
`writer_id`=(@lastDependency:=`writer_id`), -- Set writer_id of current row
position=( 
   IF (
       NOT @lastDependency=@previousDependency,
           @position:=1,            -- New writer_id => set position to 1
           @position:=@position+1   -- Same writer id, increment position
   )
), 
`writer_id`=(@previousDependency:=`writer_id`) -- Set writer_id of last used row 
ORDER BY `writer_id`, position ASC -- Order by current positions

I can also use PHP to loop through all my records and save them one by one, but I guess that won't be any better

if you're ordering by writer_id, why is the position necessary? you'd just be adding yet another incrementing integer when you've already got the writer_id, which is presumably an auto_increment int — Marc B
– Marc B, Commented Oct 1, 2012 at 19:19
writer_id is not an autoincrement value here. @RomanKonz that's actually a better method, I didn't think of that. Thanks man — NLZ
– NLZ, Commented Oct 1, 2012 at 20:16
@RomanKonz, perhaps you'd like to turn that “trigger” suggestion into an answer. — MvG
– MvG, Commented Oct 2, 2012 at 5:33

RomanKonz · Accepted Answer · 2012-10-02 06:10:26Z

1

why don't you use a trigger with the following function:

"when a row is deletet, reduce evere writer_id that is greater than the deleted ones by one"

or to say it in pseudo-code:

create trigger for delete...
    update book 
    set writer_id = writer_id - 1 
    where writer_id > deleted.writer_id

answered Oct 2, 2012 at 6:10

RomanKonz

1,0271 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MvG · Accepted Answer · 2012-10-02 05:31:30Z

0

Let me quote the MySQL documentation to you:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server.

So the way you attempt to do things may work, but comes with absolutely no guarantees. Therefore I'd suggest you do this in PHP instead of MySQL. Or you do it in MySQL using a stored procedure.

answered Oct 2, 2012 at 5:31

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Collectives™ on Stack Overflow

Mysql increment in loop by variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related