Re: syntax help - updating multiple rows

On Jan 29, 1:38 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> 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...)

...thanks. that looks cool, and ive implemented something my realworld  version of it here:

UPDATE COFE_TBLINSPECTION_RPT ir1

SET (ir1.Comments, ir1.Destination) =

    (
    --get source rows
    SELECT

        ir2.Comments,
        ir2.Destination

        ir2.Package_to_PartID = pp.Package_to_PartID AND
         pp.PackageID = pk.PackageID AND
         pk.PO_Number = po.PO_Number AND
         ir2.StageID = 0 AND --passed in source-stage
         po.PO_Number = 4500087879  --passed in po number

WHERE
    --get target rows
    ir1.Package_to_PartID = ir2.Package_to_PartID AND --limit to same- tag rows

    ir1.StageID = 2 AND --passed in destination-stage     GetPoByPkgToPartId(ir1.Package_to_PartID) = 4500087879; --passed in po number

...but it fails to execute -- it breaks on the final WHERE clause, the first criteria:

WHERE
    --get target rows
    ir1.Package_to_PartID = ir2.Package_to_PartID AND --limit to same- tag rows

...says:

   "IR2"."PACKAGE_TO_PARTID": invalid identifier.

...i know the table & column name is right. appears it cant use the "ir2" alias which is inside of the nested SELECT, maybe...?

thanks,
sm Received on Tue Jan 29 2008 - 17:59:58 CST