I need to insert identifiers (numbers) into a temporary table which satisfy several conditions. I use insert into select structure. One of the conditions is the next. There are tables received_posts_1(id,post_id), received_posts_2(id,post_id)... Each selected identifier is part of a table name with received posts. I need to add an and part into the where clause of the next form.
and not exists(select 1 from CURRENT_RECEIVED_POSTS_TABLE where id = device_id and post_id = post_id_)
The insertion is in while cycle. The stop condition is a needed count of identifiers to be inserted.
Having multiple tables with the same structure is generally a sign of a poor database design. In general, it is much better to have a single table, with columns that distinguish what you are trying to do.
One approach is to create such a table using a view:
create view received_posts
select 1 as which, r.* from received_posts_1 r union all
select 2, r.* from received_posts_2 r union all
. . .;
You can then use this table in your query.
A more efficient method is to repeat the exists, with the right conditions:
not exists(select 1
from received_posts_1
where id = device_id and post_id = 1) and
not exists(select 1
from received_posts_2
where id = device_id and post_id = 2) and
. . .
As mentioned in the comments, you can use dynamic SQL if you know which post table you need for a particular invocation of the query.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments