In my current DBMS I can use
create table t1 ( f1 int, f2 int );
create table t2 ( f3 int, f4 int );
update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4
This does not work in Postgres.
How to convert this statement to Postgres 8.1 ?
Andrus.
(end of broadcast)
TIP 5: don't forget to increase your free space map settings

, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote:
Andrus wrote:
In my current DBMS I can use

create table t1 ( f1 int, f2 int );
create table t2 ( f3 int, f4 int );
update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4

That looks like a self-join on t1 without using an alias for the second
instance of t1.

I think you meant:
update t1 set f1=t2.f3 from t2 where f2 = t2.f4

is this not effectively an INNER JIN ?
the P needed a LEFT JIN.

gnari

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

, 2006-12-08 at 10:17 +0000, Ragnar wrote:
, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote:
Andrus wrote:
In my current DBMS I can use

create table t1 ( f1 int, f2 int );
create table t2 ( f3 int, f4 int );
update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4

That looks like a self-join on t1 without using an alias for the second
instance of t1.

I think you meant:
update t1 set f1=t2.f3 from t2 where f2 = t2.f4

is this not effectively an INNER JIN ?
the P needed a LEFT JIN.

this can be done in 2 operations easily:

update t1 set f1=t2.f3 from t2 where f2 = t2.f4;
update t1 set f1=null
where not exists (select f3 from t2 where f2=f4);

it can also be done in one operation with a
self join:
update t1 set f1=j.f3
from (t1 t1b left join t2 on t1b.f2=t2.f4) as j
where t1.f2=j.f2;

gnari

(end of broadcast)
TIP 6: explain analyze is your friend

In article <45792BC1.3040305 (AT) magproductions (DOT) nl>,
Alban Hertroys <alban (AT) magproductions (DOT) nlwrites:

Andrus wrote:
>In my current DBMS I can use
>
>create table t1 ( f1 int, f2 int );
>create table t2 ( f3 int, f4 int );
>update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4

That looks like a self-join on t1 without using an alias for the second
instance of t1.

I think you meant:
update t1 set f1=t2.f3 from t2 where f2 = t2.f4

this one:

UPDATE t1
SET f1 = t2.f3
FRM t1 x
LEFT JIN t2 N x.f2 = t2.f4
WHERE x.f2 = t1.f2

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

Ragnar <gnari (AT) hive (DOT) iswrites:
, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote:
>Andrus wrote:
update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4
>
>That looks like a self-join on t1 without using an alias for the second
>instance of t1.
>
>I think you meant:
>update t1 set f1=t2.f3 from t2 where f2 = t2.f4

is this not effectively an INNER JIN ?
the P needed a LEFT JIN.

I think using a join for this at all is bad style. What if there is
more than one t2 match for a specific t1 row? You'll get indeterminate
results, which is not a very good thing for an UPDATE. In this
particular example you could do

update t1 set f1 = (select f3 from t2 where t1.f2=t2.f4);

This will update to f3 if there's exactly one match, update to NULL if
there's no match (which is what I assume the P wants, since he's using
a left join), and raise an error if there's multiple matches. If
you need to not fail when there's multiple matches, think of a way to
choose which one you want, perhaps the largest f3:

update t1 set f1 = (select max(f3) from t2 where t1.f2=t2.f4);

course, you could work out a way to make the join determinate too.
My point is that if you're in the habit of doing this sort of thing
via join, some day you will get careless and get screwed by an
indeterminate update. If you're in the habit of doing it via subselects
then the notation protects you against failing to think about the
possibility of multiple matches. (Possibly this explains why there is
no such construct as UPDATE FRM in the SQL standard)

The problem with the subselect approach of course is what if you need to
transfer multiple columns from the other table row? You could do

update t1 set f1 = (select f3 from t2 where t1.f2=t2.f4),
f2 = (select f7 from t2 where t1.f2=t2.f4),
f3 = (select f9 from t2 where t1.f2=t2.f4);

This works but is just as inefficient as it looks. The SQL spec
does have an answer:

update t1 set (f1,f2,f3) = (select f3,f7,f9 from t2 where t1.f2=t2.f4);

but PG does not support that syntax yet :-(. I'd like to see it in 8.3
though

regards, tom lane

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