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!