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

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!