Обсуждение: Help with "missing FROM clause" needed
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?"
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 ===
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
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?"
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 ===
"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