Обсуждение: "INNER JOIN .... USING " in an UPDATE

Hi all,

(Version 3.5.5)

I have tried to figure this out, but the docs, google, and my all
imagination fail me. I want to use a join clause with a "using list"
in an update statement.  The following works, but it uses the WHERE
version of a join:

update new_pivoted_table a set "2008-11-10" = b.data_stuff from
test_pivot b  where a.id=b.id and a.id2=b.id2 and
date_ex='2008-11-10';
UPDATE 3

The following doesn't work,  to my chagrin:

wsprague=# update new_pivoted_table a set "2008-11-10" = b.data_stuff
from test_pivot b join a using (id, id2) where  date_ex='2008-11-10';
ERROR:  relation "a" does not exist

Neither does this:

wsprague=# update new_pivoted_table set "2008-11-10" = b.data_stuff
from test_pivot b join new_pivoted_table using (id, id2) where
date_ex='2008-11-10';
ERROR:  table name "new_pivoted_table" specified more than once

The following gives the wrong answer (updates five rows all to 2 -- wrong):

update new_pivoted_table set "2008-11-10" = b.data_stuff from
test_pivot b join new_pivoted_table c using (id, id2) where
date_ex='2008-11-10';

I would REALLY LIKE to use the using clause, because it works great in
autogenerated code for EXECUTE statements, if at all possible.  I also
would like to avoid iterating.

Here is "test_pivot":

 id | id2 |  date_ex   | data_stuff
----+-----+------------+------------
  1 | one | 2008-10-10 |          1
  1 | one | 2008-11-10 |          2
  2 | one | 2008-11-10 |          3
  1 | one | 2008-12-10 |          4
  2 | one | 2008-12-10 |          5
  3 | one | 2008-12-10 |          6
  1 | two | 2008-11-10 |          7
  1 | two | 2008-11-10 |          8
  2 | two | 2008-10-10 |          9

Here is new_pivoted_table:

 id | id2 | 2008-10-10 | 2008-11-10 | 2008-12-10
----+-----+------------+------------+------------
  1 | one |            |            |
  1 | two |            |            |
  2 | one |            |            |
  2 | two |            |            |
  3 | one |            |            |

Tx!

Webb Sprague wrote:
> Hi all,
>
> (Version 3.5.5)

I'd upgrade - version 3.5.5 doesn't actually exist :-)

> I have tried to figure this out, but the docs, google, and my all
> imagination fail me. I want to use a join clause with a "using list"
> in an update statement.  The following works, but it uses the WHERE
> version of a join:
>
> update new_pivoted_table a set "2008-11-10" = b.data_stuff from
> test_pivot b  where a.id=b.id and a.id2=b.id2 and
> date_ex='2008-11-10';
> UPDATE 3
>
> The following doesn't work,  to my chagrin:
>
> wsprague=# update new_pivoted_table a set "2008-11-10" = b.data_stuff
> from test_pivot b join a using (id, id2) where  date_ex='2008-11-10';
> ERROR:  relation "a" does not exist

I think you're out of luck. The UPDATE ... FROM bit is a PostgreSQL
extension in itself, the standard way to do this would be:

UPDATE t1 ... WHERE (id,id2) IN (SELECT id,id2 FROM t1 JOIN t2 USING ...)

--
  Richard Huxton
  Archonet Ltd