Thread: foreach statment?

Hi,

I have a situation (that I seem to come across often - hence the
question) where I have 3 tables.

items
items_options
options

items has a many-to-many relationship with options so items_options is
merely a link table with an item_id and and option_id.

What I need to do is give a default option to all items that don't have
any options.

I would do something like this:

select i.item_id from items i where (select count(item_id) from
items_options where item_id=i.item_id) = 0;

And then write a script that will go through the outputted list of
item_id's and for each one do an insert statement like:

insert into items_options values ($item_id, $n);

Where $n is the number of my default option.

My question is this. Is there any way of performing the above in SQL
through the psql command interface without having to write a throw-away
script to do the job for me? It would need some sort of "foreach"
operation for the select statement. Does any such operation exist?

Regards,

Abdul-Wahid


--
Abdul-Wahid Paterson

Lintrix Networking & Communications ltd.
Web: http://www.lintrix.net/
Tel: +44 7801 070621
Email/Jabber: aw@lintrix.net
--------------------------------------------------------------------
Web-Hosting  |  Development  |  Security  |  Consultancy  |  Domains
--------------------------------------------------------------------

On 19 May 2003, Abdul-wahid Paterson wrote:

> Hi,
>
> I have a situation (that I seem to come across often - hence the
> question) where I have 3 tables.
>
> items
> items_options
> options
>
> items has a many-to-many relationship with options so items_options is
> merely a link table with an item_id and and option_id.
>
> What I need to do is give a default option to all items that don't have
> any options.
>
> I would do something like this:
>
> select i.item_id from items i where (select count(item_id) from
> items_options where item_id=i.item_id) = 0;
>
> And then write a script that will go through the outputted list of
> item_id's and for each one do an insert statement like:
>
> insert into items_options values ($item_id, $n);

Maybe something like (not really tested)

insert into items_options select item_id, $n from
 items i where not exists (select item_id from item_options where
  i.item_id=item_id);

Abdul-wahid Paterson <aw@lintrix.net> writes:
> I would do something like this:

> select i.item_id from items i where (select count(item_id) from
> items_options where item_id=i.item_id) = 0;

> And then write a script that will go through the outputted list of
> item_id's and for each one do an insert statement like:

> insert into items_options values ($item_id, $n);

> Where $n is the number of my default option.

Use INSERT/SELECT:

insert into items_options
   select i.item_id, $n
   from items i where (select count(item_id) from
                       items_options where item_id=i.item_id) = 0;

BTW, I'd think about a NOT EXISTS instead of a COUNT test in the
WHERE ... should run faster ...

            regards, tom lane