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