Обсуждение: Why doesn't update syntax match insert syntax?

Поиск
Список
Период
Сортировка

Why doesn't update syntax match insert syntax?

От
Rob Richardson
Дата:
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
ofinner covers containing a name field and fields named x and y to track where each cover is, and you have another
tableof permissible locations for inner covers and other things, with fields containing the name of the stored item,
itstype, 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
alreadyupdated the storage location table, and now I want to update the locations in the inner cover table.  So I want
todo 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

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

От
Merlin Moncure
Дата:
On Thu, Oct 10, 2013 at 10:03 AM, Rob Richardson
<RDRichardson@rad-con.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
ofinner covers containing a name field and fields named x and y to track where each cover is, and you have another
tableof permissible locations for inner covers and other things, with fields containing the name of the stored item,
itstype, 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
alreadyupdated the storage location table, and now I want to update the locations in the inner cover table.  So I want
todo 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

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

От
Marc Mamin
Дата:
> On Thu, Oct 10, 2013 at 10:03 AM, Rob Richardson
> <RDRichardson@rad-con.com> wrote:
>
> > 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

Hi,

an alternate syntax which is nearer to the INSERT one:

update foo set (a,b) = (bar.a, bar.b)
FROM bar
WHERE foo.id = bar.id;

I guess this is just a question of taste...

regards,

Marc Mamin