Re: syntax help - updating multiple rows

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Thu, 31 Jan 2008 20:10:50 +0100
Message-ID: <9cf9b$47a21d3a$524b5c40$17870@cache6.tilbu1.nb.home.nl>

SpaceMarine wrote:

> 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...?
>

Of course - the join should be within the inner, not the outer. You are correct.

Inner and outer have their respective where clauses. You still seem to miss some tables in the from clause, where you do have aliases:

UPDATE COFE_TBLINSPECTION_RPT ir1
SET (ir1.Comments, ir1.Destination) =

    (
    SELECT

        ir2.Comments,
        ir2.Destination
    FROM
	COFE_TBLINSPECTION_RPT ir2
      , some_other_table pp
      , yet_another pk
      , last_one po
    WHERE
    	ir1.Package_to_PartID = ir2.Package_to_PartID

    AND 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 po.PO_Number = 4500087879
   )
WHERE
    --get target rows
    ir1.StageID = 2 AND --passed in destination-stage     GetPoByPkgToPartId(ir1.Package_to_PartID) = 4500087879; --passed in po number

-- 

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

Received on Thu Jan 31 2008 - 13:10:50 CST