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
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? |