MySQL Iterate
- hallsofvallhalla
- Site Admin
- Posts: 12031
- Joined: Wed Apr 22, 2009 11:29 pm
MySQL Iterate
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#.
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#.
- Jackolantern
- Posts: 10893
- Joined: Wed Jul 01, 2009 11:00 pm
Re: MySQL Iterate
This may work:
I know you can use subqueries in the FROM clause.
Code: Select all
SELECT *
FROM (SELECT tablenames FROM tablelist) AS ttable
WHERE ttable.value > 3;
The indelible lord of tl;dr
- hallsofvallhalla
- Site Admin
- Posts: 12031
- Joined: Wed Apr 22, 2009 11:29 pm
Re: MySQL Iterate
yeah but would that let me iterate through each one and make an update to the table?
- Jackolantern
- Posts: 10893
- Joined: Wed Jul 01, 2009 11:00 pm
Re: MySQL Iterate
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
- a_bertrand
- Posts: 1537
- Joined: Mon Feb 25, 2013 1:46 pm
Re: MySQL Iterate
Code: Select all
update mytable set value=value+1 where values > 10
Creator of Dot World Maker
Mad programmer and annoying composer
Mad programmer and annoying composer
- hallsofvallhalla
- Site Admin
- Posts: 12031
- Joined: Wed Apr 22, 2009 11:29 pm
Re: MySQL Iterate
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.
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.
- Jackolantern
- Posts: 10893
- Joined: Wed Jul 01, 2009 11:00 pm
Re: MySQL Iterate
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.
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
- hallsofvallhalla
- Site Admin
- Posts: 12031
- Joined: Wed Apr 22, 2009 11:29 pm
Re: MySQL Iterate
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.
in other words when people install the mod it uses .SQL file to update the users DB.
- Jackolantern
- Posts: 10893
- Joined: Wed Jul 01, 2009 11:00 pm
Re: MySQL Iterate
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