MySQL Iterate

Place for questions and answers for all newcomers and new coders. This is a free for all forum, no question is too stupid and to noob.
Post Reply
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

MySQL Iterate

Post by hallsofvallhalla »

How do I iterate through results in MySQL ONLY,

For instance I have a table that holds a list of table names. I want to query that table then make changes to all that tables in that list. I am doing this only in a .sql file. NOT PHP or C#.
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: MySQL Iterate

Post by Jackolantern »

This may work:

Code: Select all

SELECT *
FROM (SELECT tablenames FROM tablelist) AS ttable
WHERE ttable.value > 3;
I know you can use subqueries in the FROM clause.
The indelible lord of tl;dr
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: MySQL Iterate

Post by hallsofvallhalla »

yeah but would that let me iterate through each one and make an update to the table?
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: MySQL Iterate

Post by Jackolantern »

There is no iteration in SQL. Iteration is only needed in programming languages. The subquery returns a "bag" of all the results, which become an ad hoc table that can then be further manipulated by the outer query. This is always how SQL works.
The indelible lord of tl;dr
User avatar
a_bertrand
Posts: 1537
Joined: Mon Feb 25, 2013 1:46 pm

Re: MySQL Iterate

Post by a_bertrand »

Code: Select all

update mytable set value=value+1 where values > 10
You can do such things, or use sub queries... but there is no "iterate" unless you go to some stored procedure code (not sure what MySQL allows there).
Creator of Dot World Maker
Mad programmer and annoying composer
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: MySQL Iterate

Post by hallsofvallhalla »

hmm that is what I have ran into thus far. Basically I need to update Map tables in NWE based on what module you have installed. Each Map is its own Table and those names are stored in a table.

For instance I have Map A1 and A2. They are each their own tables with thousands of rows. In a Locations table I have two entries. One for each map. I am installing a Lumber module so I need to add a Lumber Column in each map table with a default and such.
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: MySQL Iterate

Post by Jackolantern »

Sorry, I just thought about that query I wrote up there, and it won't work. No idea why I thought it would pick up the contents of those tables as table names lol.

Yeah, if you want to do this in only SQL, stored procedures are the only way I know of. Outside of those, SQL is just not designed to be dynamic in that way on its own (that is work typically done in the programming language). Here is a good tutorial on them.

However, why do you need to do this in SQL? It would be far more efficient to do it in the website programming language, since this is unloading the load onto the always-burdened database server.
The indelible lord of tl;dr
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: MySQL Iterate

Post by hallsofvallhalla »

For the install with NWE. It uses SQL and has a autodection of new scripts for updates. I could do a check in the PHP code possibly and just have it run if it is missing.

in other words when people install the mod it uses .SQL file to update the users DB.
User avatar
Jackolantern
Posts: 10893
Joined: Wed Jul 01, 2009 11:00 pm

Re: MySQL Iterate

Post by Jackolantern »

Oh, I gotcha. You may need a combination of stored procedures and triggers then. I am not actually sure if you will need the triggers, but if you need the stored procedure to run in response to something else the SQL file does during setup, you may need them.
The indelible lord of tl;dr
Post Reply

Return to “Beginner Help and Support”