Can I use a query with UPDATE on its SET?
Hi Ho!
Since I can do:
INSERT INTO table (SELECT a_transaction.*);
I am wondering whether I can do:
UPDATE table SET (SELECT a_transaction.*) WHERE primary_key = (SELECT a_transaction.primary_key);
instead of:
DELETE FROM table WHERE primary_key = (SELECT a_transaction.primary_key);
INSERT INTO table (SELECT a_transaction.*);
Can I? I am using PostgreSQL 8.3.5. I get the feel that I cannot do so from reading the manual.
Thank you.
Best regards,
Eus (FSF member #4445)
In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices.
Join free software movement today! It is free as in freedom, not as in free beer!
Eus wrote:
Hi Ho!
Since I can do:
INSERT INTO table (SELECT a_transaction.*);
I am wondering whether I can do:
UPDATE table SET (SELECT a_transaction.*) WHERE primary_key = (SELECT a_transaction.primary_key);
UPDATE table SET fieldname = (SELECT ..) WHERE primary_key = (SELECT...)
--
Craig Ringer
Hi Ho!
--- On Thu, 2/26/09, Craig Ringer <craig@postnewspapers.com.au> wrote:Eus wrote:
Hi Ho!
Since I can do:
INSERT INTO table (SELECT a_transaction.*);
I am wondering whether I can do:
UPDATE table SET (SELECT a_transaction.*) WHERE
primary_key = (SELECT a_transaction.primary_key);
UPDATE table SET fieldname = (SELECT ..) WHERE primary_key
= (SELECT...)
Is it possible to eliminate the use of `fieldname' completely?
So, I just need to type `UPDATE table SET (SELECT ...) WHERE primary_key'.
I think this should be possible because if the subquery in the SET clause returns the same number of columns with the same types of those of the destination table, PostgreSQL should be able to just update the values at once.
Thanks.
--
Craig Ringer
Best regards,
Eus (FSF member #4445)
In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices.
Join free software movement today! It is free as in freedom, not as in free beer!
On Thu, Feb 26, 2009 at 02:15:49AM -0800, Eus wrote:
Is it possible to eliminate the use of `fieldname' completely?
So, I just need to type `UPDATE table SET (SELECT ...) WHERE primary_key'.I think this should be possible because if the subquery in the SET
clause returns the same number of columns with the same types of those
of the destination table, PostgreSQL should be able to just update the
values at once.
Not at the moment; there was a discussion about it a while ago[1]http://archives.postgresql.org/pgsql-hackers/2009-01/msg02336.php on the
-hackers list. It all petered out after I couldn't think how to explain
my position and have since forgotten about it. I'll try and think what
I was trying to say and respond again.
--
Sam http://samason.me.uk/
[1]: http://archives.postgresql.org/pgsql-hackers/2009-01/msg02336.php