mysql loop

ShaqDiesel · 11-04-2008, 11:35 AM

Can mysql loop through results?

I'd like to do something like:

for (select id from tablename)
select colName from tablename2 whereid="id_from_current_iteration";

TB0ne · 11-04-2008, 11:42 AM

Quote:

Originally Posted by ShaqDiesel

Can mysql loop through results?

I'd like to do something like:

for (select id from tablename)
select colName from tablename2 whereid="id_from_current_iteration";

Yes..you can either do it with a MySQL stored procedure (info here http://www.browardphp.com/mysql_manu...rocedures.html), or write a program to do it. Perl has great hooks into MySQL....

nishamathew1980 · 11-04-2008, 01:29 PM

If you are looking for something akin to cursors in PL/SQL - the closest thing MySQL offers is Procedures. But please note - it is nowhere close to as powerful and useful as cursors.

If you have pressing need to iterate through resultsets - either do it at your program layer (whichever the language) or migrate to an Oracle DB.

Linux Archive

chrism01 · 11-04-2008, 05:44 PM

Or a sub-select:

Code:

select colName from tablename2 whereid in (select id from tablename);

paulsm4 · 11-04-2008, 08:39 PM

SQL is all about using *sets*.

Even though most dialects of SQL give you ways to iterate through individual items in your "select", it's almost always a Bad Idea to fetch anything besides a complete set at the server (then feel free to iterate to your heart's content, if need be, once you get the result set back to the client).

As pointed out above:

Quote:

for (select id from tablename)
select colName from tablename2 whereid="id_from_current_iteration";
-- Different databases usually have different SQL extensions that let you do this ... but you *shouldn't* (at least not directly from SQL)

Quote:

select colName from tablename2 whereid in (select id from tablename);
-- Much, much better

Here's a good article that explains further:
http://www.paragoncorporation.com/Ar...x?ArticleID=27