1

I need to do some maintenance and changes to my database. I do not want to write PHP code to do this, I would much rather just plug in some code into the SQL execution window in phpMyAdmin.

For example I would like to set the ID value of all rows in a table +1. But since they are unique if I try to say UPDATE table SET column_ID = column_ID +1 it will yell at me, saying that it cant do duplicate IDs - of course not because it will start at ID 1, set it to 2, but ID 2 already exists.

So I would like to go ahead and do something like this: start at ID 100, and then work my way down to 1.

DECLARE @id_to_update = 100
WHILE @id_to_update >= 1
  BEGIN
    UPDATE table SET column_ID = column_ID +1 WHERE column_ID = @id_to_update
    @id_to_update = @id_to_update -1
  END

But this must not be the correct syntax...

So here are my questions:

  • How can I make the query above work?
  • What should I know about basic mySQL syntax for things OTHER than simply selecting, inserting, or updating rows? I mean creating variables, running loops, doing other programming procedures...
  • Do you have any good resources (tutorials) you can point me to? and I mean other than the mySQL manual, because I am not smart enough to understand any of whats written in there...

EDIT: Why do I want to do this? Well my database system is still sort of in a development stage, and while I am adding features and changing things around - mostly because my application is growing, but also because I am getting better ideas how to organize my stuff, I may need to make changes.

In particular on this one I am updating a database which sets definitions, that I am accessing throughout my app. I am adding a value, the IDs have been numbered with auto_increment, but now the value I am adding should logically be on top, having an ID of 1.

So think of it as if I am referencing colors. and right now I have red, yellow, green. Buy now I really need blue, and blue is actually the most important color, so I need it sorted to be on top.

I did never specify any foreign keys, I only pull the data from my DB using JOIN statements. I don't think my tables talk to each other outside of PHP...

Another example why I might need techniques like that are for general DB administration and maintenance. Also strongly determined by the fact that my app is growing, and while I am BETA testing it, I may have to change stuff around, have to do some bulk edits to the data, which I would much rather do with an atomized function (FOR, WHILE, etc.) then to click into each field and change it manually.

Hope this makes more sense now.

Aside from that: Hey, even if it brakes my stuff, I just wanna know how to get it to work. How to run code in mySQL. Again: I could simply write this up in PHP, switching back and forth between PHP code and DB queries, but since SQL seems to offer programming commands, why not use those and save the hassle of uploading and debugging PHP scripts, and simply punch in the code directly into phpMyAdmin... Think of a different example, if you like! :D

oliver_siegel
  • 1,666
  • 3
  • 22
  • 37
  • Side question, would not changing every ID on a table break ALL and every `FOREIGN KEY` relationship with that table? Have you considered that it would trash most of your data, pointing to incorrect rows? Or is that intended? Besides, you REALLY need to take your time to read and understand a manual, as it's the most important documentation of the DB engine. Tutorials are nice to learn, but often fairly limited for real-world situations. – Alejandro Aug 16 '13 at 01:11
  • I agree with Alejandro this seems to be a bad idea. You will destroy all relationships in your database i.e. logically corrupt your data. If you really need to do this make sure you have backups and back up that backup. Please provide some more detail around this. – Namphibian Aug 16 '13 at 01:37
  • I added some explanation why I don't think this will hurt my DB. Im in development anyways, so I am doing lots of restructuring still to get the system up and running. And sorry, but I look at the documentation and I see gibberish... It's so abstract and complex, I haven't had enough simple example to learn from, that I could comprehend what it says in there... :-/ – oliver_siegel Aug 16 '13 at 02:23

2 Answers2

2

MySQL Flow control statements (e.g., while, if, etc.) can only be done within stored procedures (see http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html). Here is the documentation about how to create a stored proc: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html.

Here is a basic stored procedure example to get you started:

DELIMITER $$

DROP PROCEDURE IF EXISTS `myProcedure` $$
CREATE PROCEDURE `myProcedure`() 

BEGIN
    DECLARE counter int(3) DEFAULT 0;

    WHILE (counter < 50) DO
        SELECT * from my_table where id = counter;

        set counter := counter +1;
    END WHILE;


END $$

You can then call it with:

call my_schema.myProcedure();

Note that I included the "DROP PROCEDURE IF EXISTS" because stored procedures need to be dropped before they can be recreated again, when you need to update their code.

jexx2345
  • 678
  • 2
  • 8
  • 15
  • Great thanks for the information... Like I said my head gets really dizzy when I try to read the manual... Do you know how to write the procedure I attempted to create above as a stored procedure? – oliver_siegel Aug 16 '13 at 02:33
  • Updated my original post with a basic example for you. – jexx2345 Aug 16 '13 at 03:05
  • I think I got it figured out, thanks for your help! I wasnt actually able to run the code, because it say 'access denied' I contacted my host. Also I guess phpMyAdmin has a separate Delimiter field... http://stackoverflow.com/questions/2846516/how-to-write-a-stored-procedure-using-phpmyadmin-and-how-to-use-it-through-php – oliver_siegel Aug 17 '13 at 21:32
  • Don't forget to reset the delimiter: DELIMITER ; – BoB3K Aug 02 '17 at 16:55
1

I'm not sure why you would want to change the ID itself like that. Further explanation on what you are trying to do would help. The ID really should never be changed.

As far as the query, you declare the variable like this (you don't need DECLARE for user-defined variables in MySQL)

SET @id_to_update = 100;

And the query would probably be something like this

UPDATE table SET somecolumn = somecolumn + 1 
WHERE somecolumn BETWEEN 1 AND @id_to_update;

There are a lot of things to learn, like triggers, stored procedures, indexes, backup/restore, etc.. W3schools is a good start for learning SQL (and a bunch of other languages). You really avoid writing queries as loops. You need to think in sets when writing SQL.

me1
  • 21
  • 1
  • 4