Why doesn't update syntax match insert syntax?

From: Rob Richardson <RDRichardson(at)rad-con(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Why doesn't update syntax match insert syntax?
Date: 2013-10-10 15:03:48
Message-ID: 67D108EDFAD3C148A593E6ED7DCB4BBDC3FB5ACC@RADCONWIN2K8PDC.radcon.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've been curious about this for a long time. The syntax for an INSERT query is often much easier to use, in my opinion, then the syntax for an UPDATE query. For example, and this is what I am trying to do, assume you have a table of inner covers containing a name field and fields named x and y to track where each cover is, and you have another table of permissible locations for inner covers and other things, with fields containing the name of the stored item, its type, and its x and y coordinates.

I am resetting my database to initial conditions, so I am putting the inner covers in their storage locations. I've already updated the storage location table, and now I want to update the locations in the inner cover table. So I want to do this:

UPDATE inner_covers (X, Y)
SELECT sl.X, sl.Y FROM storage_locations sl where sl.name = inner_covers.name

If I were doing an insertion, that syntax would work. But instead, I'm forced to do this:

UPDATE inner_covers
SET X = (SELECT sl.X FROM storage_locations sl where sl.name = inner_covers.name),
Y = (SELECT sl.Y FROM storage_locations sl where sl.name = inner_covers.name)

Or is there another, more convenient form of the UPDATE query that I'm not familiar with?

Thanks very much!

RobR

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rémi Cura 2013-10-10 16:15:06 Re: Tree structure
Previous Message Merlin Moncure 2013-10-10 15:02:46 Re: Why doesn't update syntax match insert syntax?