Indie-Resource.com

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#.

This may work:
I know you can use subqueries in the FROM clause.

yeah but would that let me iterate through each one and make an update to the table?

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.

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).

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.

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.

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.

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.