Обсуждение: implicit tables syntax disappeared from 8.0->8.1
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
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/interactive/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION 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 schrieb: > 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/interactive/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION Better dont do that but use the FROM clause of update: http://www.postgresql.org/docs/8.1/static/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