-1

I have a table with 2 columns: sql_command and result like that:

sql_command                  result
select count(*) from t1;     null
select count(*) from t2;     null
select count(*) from t3;     null
select count(*) from t4;     null

Is it possible execute sql command and put the result into column without loop? With loop I can do it like this way:

declare c;
for .. loop
    execute ..
    into c;
    update my_table
    set result = c
    where ...;
end loop;

But maybe there is a way without loop? With use execute in update or another way? Maybe it possible to create another table with dynamic execute, which will contain sql and result?

upd: I don't want to use loop, because I have 400+ tables and I believe, it is too much for loop.

yuoggy
  • 97
  • 1
  • 10
  • Why are you looking for a different solution? The one you have looks fine to me. Except for the obvious fact that it is not safe. But the only way to avoid it is not to run dynamic queries. – freakish Aug 23 '23 at 07:26
  • I suppose, loop is not the optimal way anyway. – yuoggy Aug 23 '23 at 09:22
  • Maybe. But why does this matter? Replacing the loop with anything else is unlikely to be noticable, even if the loop is not optimal. Because the performance will be dominated by the execution of each of those small queries, regardless of how they are retrieved. – freakish Aug 23 '23 at 09:37

1 Answers1

0

Definitely, You can do this by using SQL UPDATE statement with a subquery.

Here is an example query for your reference.

    UPDATE my_table
    SET result = (
    SELECT count(*) FROM t1 WHERE sql_command = 'select count(*) from t1'
    UNION ALL
    SELECT count(*) FROM t2 WHERE sql_command = 'select count(*) from t2'
    UNION ALL
    SELECT count(*) FROM t3 WHERE sql_command = 'select count(*) from t3'
    UNION ALL
    SELECT count(*) FROM t4 WHERE sql_command = 'select count(*) from t4'
);

By using this method, you may update every row in the my_table with only one SQL statement, eliminating the need for explicit loops.

Hope this is useful.

Raja Rakshak
  • 168
  • 2