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