Re: syntax help - updating multiple rows
From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Tue, 29 Jan 2008 20:38:15 +0100
Message-ID: <ac83e$479f80a7$524b5c40$8660@cache5.tilbu1.nb.home.nl>
>
> 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
>
Date: Tue, 29 Jan 2008 20:38:15 +0100
Message-ID: <ac83e$479f80a7$524b5c40$8660@cache5.tilbu1.nb.home.nl>
SpaceMarine wrote:
> On Jan 29, 12:44 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> 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
where
i2.inspectiontypeid = 'WHATEVER'
and i2.po = some_number)
where
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...)
-- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me upReceived on Tue Jan 29 2008 - 13:38:15 CST