code_junkie

mysql update statement behaves differently based on whether a user variable exists

Nov 12th, 2011
49
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.71 KB | None | 0 0
  1. set @last='';
  2. set @sort=NULL;
  3. update conf_profile set sort=
  4. if(
  5. @last=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)),
  6. (@sort:=@sort+1),
  7. (@sort:=0)
  8. )
  9. order by org_id,profile_type_id,page,col,sort,id;
  10.  
  11. mysql> set @sort := NULL;
  12. mysql> SELECT @sort; -- returns NULL
  13. mysql> set @sort := @sort + 1;
  14. mysql> SELECT @sort; -- returns NULL again
  15. mysql> set @sort := 0;
  16. mysql> set @sort := @sort + 1;
  17. mysql> SELECT @sort; -- returns 1
  18.  
  19. set @last='';
  20. -- set @sort=NULL;
  21. update conf_profile set sort=
  22. if(
  23. @last!=(@last:=concat(org_id,',',profile_type_id,',',page,',',col)),
  24. (@sort:=0),
  25. (@sort:=@sort+1)
  26. )
  27. order by org_id,profile_type_id,page,col,sort,id;
Add Comment
Please, Sign In to add comment