CODEWITHSUNDEEP
mysqlvariablesloopsincrement
NLZ
NLZ

Reputation: 945

Mysql increment in loop by variable

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

Upvotes: 1

Views: 2081

Answers (2)

RomanKonz
RomanKonz

Reputation: 1027

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

Upvotes: 1

MvG
MvG

Reputation: 60898

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.

Upvotes: 0

Related Questions