PostgreSQL Discussion - implicit tables syntax disappeared from 8.0->8.1

Author

implicit tables syntax disappeared from 8.0->8.1

pg@os10000.net

2006-02-28, 8:28 pm

Hello,

the following used to work:

create table t1(t1f1 text, t1f2 text, t1f3 text);
create table t2(t2f2 text, t2f3 text);

insert ...

update t1
set t1f1='test'
where t1.t1f2=t2.t2f2
and t1.t1f3=t2.t2f3;

unfortunately, now I get the error that t2 is not in the FROM clause.

I know I can do

update t1
set t1f1='test'
where t1.t1f2||'/'||t1.t1f3 in
(select t2.t2f2||'/'||t2.t2f3 from t2);

But I'm afraid that's very expensive. Do you have a suggestion for
alternative syntax for my initial query?

Thank you,

Oliver Seidel

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

A. Kretschmer

2006-02-28, 8:28 pm

am 28.02.2006, um 14:01:44 +0100 mailte pg@os10000.net folgendes:
> update t1
> set t1f1='test'
> where t1.t1f2=t2.t2f2
> and t1.t1f3=t2.t2f3;
>
> unfortunately, now I get the error that t2 is not in the FROM clause.

You can set

add_missing_from

in yout postgresql.conf, but please read
http://www.postgresql.org/docs/8.1/...PATIBLE-VERSION

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tino Wildenhain

2006-02-28, 8:28 pm

A. Kretschmer schrieb:
> am 28.02.2006, um 14:01:44 +0100 mailte pg@os10000.net folgendes:
>
>
>
> You can set
>
> add_missing_from
>
> in yout postgresql.conf, but please read
> http://www.postgresql.org/docs/8.1/...PATIBLE-VERSION

Better dont do that but use the FROM clause of update:

http://www.postgresql.org/docs/8.1/...sql-update.html

e.g.

UPDATE t1
SET t1.f1='test'
FROM t2
WHERE t1.t1f2=t2.t2f2
AND t1.t1f3=t2.t2f3;

(although I bet your example is sloppy and you even want t1, t2 and t3 -
so add t3 to the FROM list as you know with SELECT)

HTH
Tino

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links