pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoined deletes but one table being a subquery.
4+ messages / 2 participants
[nested] [flat]
* Joined deletes but one table being a subquery.
@ 2003-08-22 10:01 Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 4+ messages in thread
From: Rajesh Kumar Mallah @ 2003-08-22 10:01 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joined deletes but one table being a subquery.
@ 2003-08-22 10:23 Tomasz Myrta <jasiek@klaster.net>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tomasz Myrta @ 2003-08-22 10:23 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joined deletes but one table being a subquery.
@ 2003-08-23 09:30 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 4+ messages in thread
From: Rajesh Kumar Mallah @ 2003-08-23 09:30 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joined deletes but one table being a subquery.
@ 2003-08-25 05:59 Tomasz Myrta <jasiek@klaster.net>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-08-25 05:59 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-08-25 05:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-22 10:01 Joined deletes but one table being a subquery. Rajesh Kumar Mallah <mallah@trade-india.com>
2003-08-22 10:23 ` Tomasz Myrta <jasiek@klaster.net>
2003-08-23 09:30 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-08-25 05:59 ` Tomasz Myrta <jasiek@klaster.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox