Обсуждение: 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