mysql - How to declare variables, run loops, and do other automatizations -

i need maintenance , changes database. not want write php code this, rather plug in code sql execution window in phpmyadmin.

for example set id value of rows in table +1. since unique if try update table set column_id = column_id +1 yell @ me, saying cant duplicate ids - of course not because start @ id 1, set 2, id 2 exists.

so go ahead , this: start @ id 100, , work way down 1.

declare @id_to_update = 100 while @id_to_update >= 1   begin     update table set column_id = column_id +1 column_id = @id_to_update     @id_to_update = @id_to_update -1   end 

but must not correct syntax...

so here questions:

• how can make query above work?
• what should know basic mysql syntax things other selecting, inserting, or updating rows? mean creating variables, running loops, doing other programming procedures...
• do have resources (tutorials) can point me to? and mean other mysql manual, because not smart enough understand of whats written in there...

edit: why want this? database system still sort of in development stage, , while adding features , changing things around - because application growing, because getting better ideas how organize stuff, may need make changes.

in particular on 1 updating database sets definitions, accessing throughout app. adding value, ids have been numbered auto_increment, value adding should logically on top, having id of 1.

so think of if referencing colors. , right have red, yellow, green. buy need blue, , blue important color, need sorted on top.

i did never specify foreign keys, pull data db using join statements. don't think tables talk each other outside of php...

another example why might need techniques general db administration , maintenance. determined fact app growing, , while beta testing it, may have change stuff around, have bulk edits data, rather atomized function (for, while, etc.) click each field , change manually.

hope makes more sense now.

aside that: hey, if brakes stuff, wanna know how work. how run code in mysql. again: write in php, switching , forth between php code , db queries, since sql seems offer programming commands, why not use , save hassle of uploading , debugging php scripts, , punch in code directly phpmyadmin... think of different example, if like! :d

mysql flow control statements (e.g., while, if, etc.) can done within stored procedures (see http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html). here documentation how create stored proc: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html.

here basic stored procedure example started:

delimiter $$  drop procedure if exists `myprocedure` $$ create procedure `myprocedure`()   begin     declare counter int(3) default 0;      while (counter < 50)         select * my_table id = counter;          set counter := counter +1;     end while;   end $$ 

you can call with:

call my_schema.myprocedure(); 

note included "drop procedure if exists" because stored procedures need dropped before can recreated again, when need update code.