title: Shorten sql queries
body: <p>I do 3 queries to reorder the rows of my table</p>
  
  <pre><code>SELECT @i:=0;
  UPDATE imagedata SET imagedata.rank = 0;
  UPDATE imagedata SET imagedata.rank = (SELECT @i:=@i+1) WHERE imagedata.kategorieID = 1;
  </code></pre>
  
  <p>which I execute from top to bottom.</p>
  
  <p>Question: Is ther a shorter way to do this?</p>
  
  <hr>
  
  <p>thx for all the feedback..
  but i have a differnet Idear now:
  Somehow I have to "connect" the "kategorieID" with the "id(Primary key)" collumn
  Therefore I need to store both information into the "rank" collumn in the format like:</p>
  
  <p>cxxx</p>
  
  <p>c=Category Number(0 to 4)
  xxx = id (1 to ...n)..unique!</p>
  
  <p>exampl.: output:</p>
  
  <pre><code>   rank
   +------+
   + 1001 +
   + 1002 +
   + 1003 +
   + ...  +
   + 1018 +
   + ...  +
   + 2001 +
   + 2002 +
   + 1019 +
   + 1020 +
  </code></pre>
  
  <p>so far I did it like:</p>
  
  <pre><code>UPDATE imagedata SET imagedata.rank = (SELECT CONCAT(kategorieID,"",LPAD(id,3,'0')) ) WHERE id=88;
  </code></pre>
  
  <p>once that "rank" is filled with data it gives me the abillity to swap 2 id's.</p>
  
  <p>Therefore I have to:
  1) get "rank" collumn of id_1 and id_2
  2) get the substring ('xxx') of that string exampl: ("004" .. "012")
  3) exchange substrings of id_1 "rank" and id_2 "rank" </p>
  
  <pre><code>SELECT @ix1:=SUBSTRING(rank, -3) FROM imagedata WHERE id=88;
  SELECT @ix2:=SUBSTRING(rank, -3) FROM imagedata WHERE id=83;
  </code></pre>
  
  <p>..I know how to get it but dont know how to exchange it? </p>