Search Postgresql Archives

"INNER JOIN .... USING " in an UPDATE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux