Re: Why doesn't update syntax match insert syntax?

On Thu, Oct 10, 2013 at 10:03 AM, Rob Richardson
<RDRichardson(at)rad-con(dot)com> wrote:
> 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!

you have UPDATE FROM:

UPDATE foo SET a=bar.a, b=bar.b
FROM bar WHERE foo.id = bar.id;

merlin