Query within a query?

[Oatley]

Hello, I have a very quick question. I have written a while loop and have found myself producing two additional queries within the loop of the query. Is this bad practice?

Thank you.

[Fou-Lu]

Its really bad practice.
Writing a query within a loop results in querying n times where n is governed by the size of the loop. With a query that returns a resultset of 50 and a loop that queries twice more is a minimum of 100 + 1 queries per load.
DBMS can be locked down to a maximum number of queries (per hour I believe). Say its locked down to 20,000 per hour, that means you can only execute the script 200 times before you cannot query any more for the remaining hour. It doesn't take long to run through 200 executions.
It will also be slowish. Since you need to keep going back to the dbms for more information you are shuffling an external connection for each one of these resultsets.

Change your query to a join and that problem will go from 101 queries to 1.

[Oatley]

Thanks Fou-Lu. That's a great piece of advice.

I have one scenario though, which I'm stuck on then. I have 'table_a' which contains a number of entries and a column called 'id' and another column called 'is_set' which contains a value of 0 or 1.

I wish to find all the Id's in 'table_a' which have a value in 'is_set' of 1

Then UPDATE a different table 'table_b' which also has the columns 'id' and 'is_set' finding each of the entries where table_a.id = table_b.id

So best way I can think of doing this would be in a loop, which is why I asked the question. Is there therefore. Better way of handing this?

Thank you

[Dormilich]

that can be done by a simple join.

SELECT
    -- fields here
FROM
    table_a
JOIN
    table_b USING (id)
WHERE
    table_a.is_set = 1;

[Inigoesdr]

Originally Posted by Dormilich

that can be done by a simple join.

Also, make sure you have keys on the field you are joining on. The performance will be quite a bit better, especially on larger tables.