Re: syntax help - updating multiple rows

From: Frank van Bortel <>
Date: Tue, 29 Jan 2008 20:38:15 +0100
Message-ID: <ac83e$479f80a7$524b5c40$>

SpaceMarine wrote:
> On Jan 29, 12:44 pm, Frank van Bortel <>
> wrote:

>> Not sure what you mean by "my desired column values";
>> do you supply these, or are these from the table?

> thanks, frank. put simpler what i mean is, "i want to update target
> rows w/ values from source rows". so i want to run a query that
> returns a set of source rows. then, i want to update some target rows
> (that exist in this same table) w/ the values of the source rows.
> id bet this is standard/simple, but i just dont know how. i found one
> solution:
> 1) get resultset of source rows; place into CURSOR
> 2) loop each row of source rows
> 3) run UPDATE statements onto target row, using loop-row's values.
> ...that could work. but i think theres probably a way to do it into a
> single statement.
> see what i mean?
> sm

Something like:
update inspection_reports i1
set (i1.col1, i1.col2, i1.col3) =
  (select i2.col1, i2.col2, i2.col3
   from inspection_reports i2
   i2.inspectiontypeid = 'WHATEVER'
   and i2.po = some_number)
i1.some_column = i2.some_column
[and i1.another_col = i2.another_col]

Make sure you have the join condition(s) right - you are updating a set with a set - if there's no common denominator you're in for a surprise (like updating every result record x times, where x is the total # of rows in table...)


Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up
Received on Tue Jan 29 2008 - 13:38:15 CST

Original text of this message