Обсуждение: Joined deletes but one table being a subquery.

Hi Folks,

DELETE  from eyp_listing where userid=t_a.userid and category_id=t_a.category_id;
such queries work perfectly.

but if t_a is a subquery how to accomplish the delete.

Regds
Mallah.

> Hi Folks,
> 
> DELETE  from eyp_listing where userid=t_a.userid and category_id=t_a.category_id;
> such queries work perfectly.
> 
> but if t_a is a subquery how to accomplish the delete.
What kind of subquery it is? Exist/Not exist doesn't work?

Regards,
Tomasz Myrta

I apologize for the silence.

t_a as been created as 

CREATE TABLE t_a as  SELECT userid,category_id from eyp_listing where userid=21742 and size ilike '%WEBFL%'
EXCEPT SELECT userid,category_id from company_export_profile where userid=21742 ;

so the subquery is basically 

( SELECT userid,category_id from eyp_listing where userid=21742 and size ilike '%WEBFL%' EXCEPT SELECT
userid,category_idfrom company_export_profilewhere userid=21742 )
 


regds
mallah.

On Friday 22 Aug 2003 3:53 pm, Tomasz Myrta wrote:
> > Hi Folks,
> >
> > DELETE  from eyp_listing where userid=t_a.userid and
> > category_id=t_a.category_id; such queries work perfectly.
> >
> > but if t_a is a subquery how to accomplish the delete.
>
> What kind of subquery it is? Exist/Not exist doesn't work?
>
> Regards,
> Tomasz Myrta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

> I apologize for the silence.
> 
> t_a as been created as 
> 
> CREATE TABLE t_a as  SELECT userid,category_id from eyp_listing where userid=21742 and size ilike '%WEBFL%'
> EXCEPT SELECT userid,category_id from company_export_profile where userid=21742 ;
> 
> so the subquery is basically 
> 
> ( SELECT userid,category_id from eyp_listing where userid=21742 and 
>  size ilike '%WEBFL%' EXCEPT SELECT userid,category_id from company_export_profile
>  where userid=21742 )
> 
> 
> regds
> mallah.
What about this?
delete from eyp_listing where exists (select * from  eyp_listing el  left join company_export ce using
(userid,category_id)where el.userid=21742 and size ilike '%WEBFL%' and ce.userid is null  and
el.userid=eyp_listing.userid and el.category_id=eyp_listing.category_id);
 

It should be solution, but not the answer for your question...
How did you use that sub-select, so it didn't work?

Regards,
Tomasz Myrta