Need SQL Help

["Mary Jo Whitcomb" <mjwhit@xxxxxxxxxxxxxxx>]

I am trying to learn SQL.  I am trying to update a field in a file with
information from a field in another file for our Inventory Managment module.
File MAPITEM has a field ITITEM with the item number and a field ITCHRG with
the patient charge number in it.  It has only one record for each item.
File MAPILOC has a field ILITEM for the item number and ILPCHG that is
supposed to have the patient charge number in it.  It has a record for each
item and for each location that the item is in.
The charge number is missing in many of the MAPILOC records and this is
causing patients to not be charged.  I thought this would be an excellent
time to use SQL, however I keep getting a message that I am trying to update
more than one row.  It's right, I am trying to update any row that has a
missing charge number.

  update usrmjw/mapiloc set (ilpchg) = (select itchrg from
 usrmjw/mapitem where mapitem.ititem = mapiloc.ilitem and
 mapiloc.ilpchg is not null)

I've looked SQL By Example and the SQL Reference manual and am getting
nowhere.  I've tried ANY and ALL and get invalid token.  The not null is in
there because I got an error that ILITEM cannot be null without it.  The
field shoud be blank, not null, but it bombs when I put in mapiloc.ilpchg =
' '

Is it possible to update any and all instances of ilpchg with the charge
number from mapitem with SQL? Any help is gratefully appreciated.
Mary Jo


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---