Re: SQL UPDATE: Update a field with a field from another file

[CRPence <CRPbottle@xxxxxxxxx>]

  Eliminate the subquery by replacing it with a literal, then 
notice what the UPDATE statement looks like:

   UPDATE ITEMBL A
   SET A.ITCLS = 'literal' /* no subquery */
   WHERE A.ITNBR = B.ITNBR AND A.HOUSE = 'JTG'

  Notice that there is no longer a definition for the column 
qualifier B in the statement; only identifier A for the ITEMBL 
table.  The scope of the original qualifier B is limited to the 
subquery, whereas the scope of the qualifier A from the UPDATE 
statement carries into the subquery.

  The subquery already implements the join between those fields, so 
likely what is desired, is to omit the "A.ITNBR = B.ITNBR AND". 
However then to update only those values matching the same join, add 
the subquery to an EXISTS; i.e. add "AND EXISTS (the_subquery)"

Regards, Chuck

Peter_Vidal@xxxxxxxx wrote:
> This is "my complication" on this simple SQL:
>
> =======================================================
> FILE:ITEMBL
> FIELDS:
> ITNBR  - ITEM NUMBER (KEY FIELD)
> HOUSE  - WAREHOUSE   (KEY FIELD)
> ITCLS  - ITEM CLASS
>
> FILE:ITMRVA
> FIELDS:
> ITNBR  - ITEM NUMBER (KEY FIELD)
> STID   - SITE ID
> ITCLS  - ITEM CLASS
>
> Goal: Update ITCLS in ITEMBL with ITCLS in ITMRVA only if:
> a) STID = 'J2P'
> b) HOUSE = 'JTG'
> c) ITEMBL.ITCLS <> ITMRVA.ITCLS 
> =======================================================
>
> This is the SQL I am doing:
>
>   UPDATE ITEMBL A SET A.ITCLS = 
>   (SELECT ITCLS FROM ITMRVA B 
>    WHERE A.ITNBR = B.ITNBR AND B.STID = 'J2P'
>     AND A.ITCLS <> B.ITCLS
>   )
>   WHERE A.ITNBR = B.ITNBR AND A.HOUSE = 'JTG' 
>                   ? 
>  
> The error I am having is: COLUMN QUALIFIER OR TABLE B UNDEFINED. 
> If I remove the "B" qualifier from the last B.ITNBR in the SQL, 
> then I have this error instead: NULL VALUES NOT ALLOWED IN COLUMN
> OR VARIABLE ITCLS.
>
> I can't see what I am doing wrong ... HELP! Thanks in advance!