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
