SQL sql query reduction

Question

I make 3 queries to reorder the rows of the table

SELECT @i:=0; UPDATE imagedata SET imagedata.rank = 0; UPDATE imagedata SET imagedata.rank = (SELECT @i: =@i +1) WHERE imagedata.kategorieID = 1; 

which I perform from top to bottom.

Question: Is there a shorter way to do this?

---

Thanks for all the feedback. but I have a differnet Idear now: Anyway, I need to "connect" "kategorieID" with "id (Primary key)" collumn Therefore I need to save both information in the "rank" column in the format, for example:

Xxx

c = Category number (0 to 4) xxx = id (1 to ... n) .. unique!

Example: output:

  rank +------+ + 1001 + + 1002 + + 1003 + + ... + + 1018 + + ... + + 2001 + + 2002 + + 1019 + + 1020 + 

so far i liked it:

 UPDATE imagedata SET imagedata.rank = (SELECT CONCAT(kategorieID,"",LPAD(id,3,'0')) ) WHERE id=88; 

as soon as this "rank" is filled with data, it gives me the opportunity to change 2 identifiers.

So I have to: 1) get the column "rank" id_1 and id_2 2) get the substring ('xxx') of this string exam: ("004" .. "012") 3) the exchange substrings id_1 "rank" and id_2 "rank"

 SELECT @ix1:=SUBSTRING(rank, -3) FROM imagedata WHERE id=88; SELECT @ix2:=SUBSTRING(rank, -3) FROM imagedata WHERE id=83; 

.. I know how to get it, but I don’t know how to replace it?

Answer 1

I would modify the second query only to update rows that will NOT be updated in the third, for example:

 SELECT @i:=0; UPDATE imagedata SET imagedata.rank = 0 WHERE imagedata.kategorieID <> 1; UPDATE imagedata SET imagedata.rank = (SELECT @i: =@i +1) WHERE imagedata.kategorieID = 1; 

EDIT: Comment on Lamak, if kategorieID can be NULL , you will want to do this instead:

 SELECT @i:=0; UPDATE imagedata SET imagedata.rank = 0 WHERE imagedata.kategorieID <> 1 OR imagedata.kategorieID IS NULL; UPDATE imagedata SET imagedata.rank = (SELECT @i: =@i +1) WHERE imagedata.kategorieID = 1; 

In addition, it is possible that this solution may be slower than yours, especially if there is no index on kategorieID . I say this because although you are updating fewer rows, now you need to filter this WHERE . Perhaps you should check the performance of this first to make sure that it is really faster.