pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with "missing FROM clause" needed
6+ messages / 4 participants
[nested] [flat]
* Help with "missing FROM clause" needed
@ 2006-03-06 13:25 Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 13:55 ` Re: Help with "missing FROM clause" needed Richard Huxton <dev@archonet.com>
2006-03-06 13:57 ` Re: Help with "missing FROM clause" needed A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 2 replies; 6+ messages in thread
From: Thomas Beutin @ 2006-03-06 13:25 UTC (permalink / raw)
To: pgsql-sql
Hi,
to be compatible with the postgres standard syntax in 8.1.x i need some
help for rewriting my "delete" statements ("select" is not a problem). I
use the following statement:
DELETE FROM partner_zu
WHERE partner_zu.pa_id = partner.id
AND partner_zu.m_id = '25'
AND partner.open = 'm'
AND partner.a_id = partner_zu.a_id
AND partner_zu.a_id = '104335887112347';
I need to delete some entries in partner_zu but the decision which to
delete is to be made by an entry in the table partner.
There is no foreign key from partner_zu to partner (bad design, i
know...) and i need a single (and hopefully performant) statement to do
the job.
Any help is gratefully appreciated!
-tb
--
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with "missing FROM clause" needed
2006-03-06 13:25 Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
@ 2006-03-06 13:55 ` Richard Huxton <dev@archonet.com>
2006-03-06 14:27 ` Re: Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2006-03-06 13:55 UTC (permalink / raw)
To: Thomas Beutin <psql@laokoon.IN-Berlin.DE>; +Cc: pgsql-sql
Thomas Beutin wrote:
> Hi,
>
> to be compatible with the postgres standard syntax in 8.1.x i need some
> help for rewriting my "delete" statements ("select" is not a problem). I
> use the following statement:
>
> DELETE FROM partner_zu
> WHERE partner_zu.pa_id = partner.id
> AND partner_zu.m_id = '25'
> AND partner.open = 'm'
> AND partner.a_id = partner_zu.a_id
> AND partner_zu.a_id = '104335887112347';
DELETE FROM partner_zu
WHERE
partner_zu.m_id = '25'
...etc...
AND partner_zu.pa_id IN (
SELECT id FROM partner WHERE open='m' AND a_id='104335887112347'
)
Does that work for you?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with "missing FROM clause" needed
2006-03-06 13:25 Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 13:55 ` Re: Help with "missing FROM clause" needed Richard Huxton <dev@archonet.com>
@ 2006-03-06 14:27 ` Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 14:51 ` Re: Help with "missing FROM clause" needed A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Beutin @ 2006-03-06 14:27 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Richard Huxton wrote:
> Thomas Beutin wrote:
>
>> Hi,
>>
>> to be compatible with the postgres standard syntax in 8.1.x i need
>> some help for rewriting my "delete" statements ("select" is not a
>> problem). I use the following statement:
>>
>> DELETE FROM partner_zu
>> WHERE partner_zu.pa_id = partner.id
>> AND partner_zu.m_id = '25'
>> AND partner.open = 'm'
>> AND partner.a_id = partner_zu.a_id
>> AND partner_zu.a_id = '104335887112347';
>
>
> DELETE FROM partner_zu
> WHERE
> partner_zu.m_id = '25'
> ...etc...
> AND partner_zu.pa_id IN (
> SELECT id FROM partner WHERE open='m' AND a_id='104335887112347'
> )
>
> Does that work for you?
What should i do with the "AND partner.a_id = partner_zu.a_id" ? Without
this it would be working fine.
Greetings,
-tb
--
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with "missing FROM clause" needed
2006-03-06 13:25 Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 13:55 ` Re: Help with "missing FROM clause" needed Richard Huxton <dev@archonet.com>
2006-03-06 14:27 ` Re: Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
@ 2006-03-06 14:51 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-03-06 15:55 ` Re: Help with "missing FROM clause" needed Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2006-03-06 14:51 UTC (permalink / raw)
To: pgsql-sql
am 06.03.2006, um 15:27:54 +0100 mailte Thomas Beutin folgendes:
> >>DELETE FROM partner_zu
> >>WHERE partner_zu.pa_id = partner.id
> >>AND partner_zu.m_id = '25'
> >>AND partner.open = 'm'
> >>AND partner.a_id = partner_zu.a_id
> >>AND partner_zu.a_id = '104335887112347';
> >DELETE FROM partner_zu
> >WHERE
> > partner_zu.m_id = '25'
> > ...etc...
> > AND partner_zu.pa_id IN (
> > SELECT id FROM partner WHERE open='m' AND a_id='104335887112347'
> > )
> >Does that work for you?
> What should i do with the "AND partner.a_id = partner_zu.a_id" ? Without
> this it would be working fine.
*untested*
DELETE FROM partner_zu using partner
WHERE partner_zu.pa_id = partner.id
...
The point is the 'using ...'
Btw.: where is my other mail?
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with "missing FROM clause" needed
2006-03-06 13:25 Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 13:55 ` Re: Help with "missing FROM clause" needed Richard Huxton <dev@archonet.com>
2006-03-06 14:27 ` Re: Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 14:51 ` Re: Help with "missing FROM clause" needed A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-03-06 15:55 ` Greg Stark <gsstark@mit.edu>
0 siblings, 0 replies; 6+ messages in thread
From: Greg Stark @ 2006-03-06 15:55 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> *untested*
> DELETE FROM partner_zu using partner
> WHERE partner_zu.pa_id = partner.id
> ...
> The point is the 'using ...'
You can also just set the add_missing_from to true for that one session if you
prefer. I don't think there's any plans to remove the option, it's just not
the default any more.
--
greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with "missing FROM clause" needed
2006-03-06 13:25 Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
@ 2006-03-06 13:57 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2006-03-06 13:57 UTC (permalink / raw)
To: pgsql-sql
am 06.03.2006, um 14:25:52 +0100 mailte Thomas Beutin folgendes:
> Hi,
>
> to be compatible with the postgres standard syntax in 8.1.x i need some
> help for rewriting my "delete" statements ("select" is not a problem). I
> use the following statement:
>
> DELETE FROM partner_zu
> WHERE partner_zu.pa_id = partner.id
> AND partner_zu.m_id = '25'
> AND partner.open = 'm'
> AND partner.a_id = partner_zu.a_id
> AND partner_zu.a_id = '104335887112347';
*untested*
delete from partner_zu using partner ...
Example:
test=# delete from only f1 where f1.id = f2.id ;
ERROR: missing FROM-clause entry for table "f2"
but:
test=# delete from only f1 using f2 where f1.id = f2.id ;
DELETE 0
Hint: read
http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-03-06 15:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-06 13:25 Help with "missing FROM clause" needed Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 13:55 ` Richard Huxton <dev@archonet.com>
2006-03-06 14:27 ` Thomas Beutin <psql@laokoon.IN-Berlin.DE>
2006-03-06 14:51 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-03-06 15:55 ` Greg Stark <gsstark@mit.edu>
2006-03-06 13:57 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
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