pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE COMPATIBILITY
5+ messages / 4 participants
[nested] [flat]
* UPDATE COMPATIBILITY
@ 2012-01-17 06:31 Gera Mel Handumon <geramel.h@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gera Mel Handumon @ 2012-01-17 06:31 UTC (permalink / raw)
To: pgsql-sql
What version of postgresql that the update compatibility below will be
implemented?
UPDATE COMPATIBILITY
UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);
TIA,
--
Gera Mel E. Handumon
-----------------------------------------------------------------
"Share your knowledge. It's a way to achieve immortality" - Dalai Lama
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UPDATE COMPATIBILITY
@ 2012-01-17 08:49 Thomas Kellerer <spam_eater@gmx.net>
parent: Gera Mel Handumon <geramel.h@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Thomas Kellerer @ 2012-01-17 08:49 UTC (permalink / raw)
To: pgsql-sql
Gera Mel Handumon, 17.01.2012 07:31:
> What version of postgresql that the update compatibility below will be
> implemented?
>
> UPDATE COMPATIBILITY
>
>
> UPDATE accounts SET (contact_last_name, contact_first_name) =
> (SELECT last_name, first_name FROM salesmen
> WHERE salesmen.id = accounts.sales_id);
None as far as I know.
You need to rewrite it to:
UPDATE accounts
SET contact_last_name = s.last_name,
contact_first_name = s.first_name
FROM salesmen s
WHERE s.id = accounts.sales_id
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UPDATE COMPATIBILITY
@ 2012-01-17 15:19 Adrian Klaver <adrian.klaver@gmail.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2012-01-17 15:19 UTC (permalink / raw)
To: pgsql-sql; +Cc: Thomas Kellerer <spam_eater@gmx.net>
On Tuesday, January 17, 2012 12:49:44 am Thomas Kellerer wrote:
> Gera Mel Handumon, 17.01.2012 07:31:
>
> None as far as I know.
>
> You need to rewrite it to:
>
> UPDATE accounts
> SET contact_last_name = s.last_name,
> contact_first_name = s.first_name
> FROM salesmen s
> WHERE s.id = accounts.sales_id
For completeness, you could also do:
UPDATE accounts
SET (contact_last_name,contact_first_name)=
(s.last_name,s.first_name)
FROM salesmen s
WHERE s.id = accounts.sales_id
Gets you a little closer to what you want:)
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UPDATE COMPATIBILITY
@ 2012-01-17 16:28 Thomas Kellerer <spam_eater@gmx.net>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2012-01-17 16:28 UTC (permalink / raw)
To: pgsql-sql
Adrian Klaver, 17.01.2012 16:19:
>> You need to rewrite it to:
>>
>> UPDATE accounts
>> SET contact_last_name = s.last_name,
>> contact_first_name = s.first_name
>> FROM salesmen s
>> WHERE s.id = accounts.sales_id
>
> For completeness, you could also do:
>
> UPDATE accounts
> SET (contact_last_name,contact_first_name)=
> (s.last_name,s.first_name)
> FROM salesmen s
> WHERE s.id = accounts.sales_id
>
Nice one!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UPDATE COMPATIBILITY
@ 2012-01-18 07:22 Samuel Gendler <sgendler@ideasculptor.com>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 0 replies; 5+ messages in thread
From: Samuel Gendler @ 2012-01-18 07:22 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
On Tue, Jan 17, 2012 at 12:49 AM, Thomas Kellerer <spam_eater@gmx.net>wrote:
> Gera Mel Handumon, 17.01.2012 07:31:
>
> What version of postgresql that the update compatibility below will be
>> implemented?
>>
>> UPDATE COMPATIBILITY
>>
>>
>> UPDATE accounts SET (contact_last_name, contact_first_name) =
>> (SELECT last_name, first_name FROM salesmen
>> WHERE salesmen.id = accounts.sales_id);
>>
>
> None as far as I know.
>
> You need to rewrite it to:
>
> UPDATE accounts
> SET contact_last_name = s.last_name,
> contact_first_name = s.first_name
> FROM salesmen s
> WHERE s.id = accounts.sales_id
>
>
You can put a query in the from clause instead of a table, I believe, too:
UPDATE accounts
SET contact_last_name = q.last_name,
contact_first_name = q.first_name
FROM (select last_name, first_name from salesmen where s.id =
accounts.sales_id)
Not any more functional than the previous example, in this case, but very
useful if you need a complicated join or aggregation/grouping.
Some useful info from the postgresql documentation on the UPDATE statement (
http://www.postgresql.org/docs/9.0/static/sql-update.html ):
"When a FROM clause is present, what essentially happens is that the target
table is joined to the tables mentioned in the fromlist, and each output
row of the join represents an update operation for the target table. When
using FROM you should ensure that the join produces at most one output row
for each row to be modified. In other words, a target row shouldn't join to
more than one row from the other table(s). If it does, then only one of the
join rows will be used to update the target row, but which one will be used
is not readily predictable.
Because of this indeterminacy, referencing other tables only within
sub-selects is safer, though often harder to read and slower than using a
join."
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-01-18 07:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-17 06:31 UPDATE COMPATIBILITY Gera Mel Handumon <geramel.h@gmail.com>
2012-01-17 08:49 ` Thomas Kellerer <spam_eater@gmx.net>
2012-01-17 15:19 ` Adrian Klaver <adrian.klaver@gmail.com>
2012-01-17 16:28 ` Thomas Kellerer <spam_eater@gmx.net>
2012-01-18 07:22 ` Samuel Gendler <sgendler@ideasculptor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox