Thread: 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 computingdevices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445
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 sametypes 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 computingdevices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445
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] 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