From: | "Webb Sprague" <webb(dot)sprague(at)gmail(dot)com> |
---|---|
To: | |
Cc: | GENERAL <pgsql-general(at)postgresql(dot)org> |
Subject: | "INNER JOIN .... USING " in an UPDATE |
Date: | 2008-11-18 04:48:46 |
Message-ID: | b11ea23c0811172048x21fa0442s1e1a3d9c81d25351@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2008-11-18 05:45:17 | Re: ERROR |
Previous Message | Eus | 2008-11-18 03:48:10 | Re: Using database to find file doublettes in my computer |