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

This is "my complication" on this simple SQL:

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.

Hi, Peter:

Alan has already sent a correction that looks good to me. I thought I would
explain that the reason it's complaining about that B qualifier is that B
was defined (confined) within your parenthesized expression. Outside of
that parenthesis set, B has no meaning.

When you removed it, you began to do the UPDATE for all rows where
A.HOUSE='JTG' - even those for which there was no matching row from the
second file. For those rows, the result was NULL (of course), and your
column apparently won't accept NULL values. (Which is a saving grace in
this case.)

I hope that helps.

Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"This is the most extraordinary collection of talent, of human knowledge,
that has ever been gathered together at the White House -- with the possible
exception of when Thomas Jefferson dined here alone."
-- John F. Kennedy (to Nobel Prize winners)