Re: DBACCESS Impossibility?????

In article <4b9pfd$q3r@news.informix.com>, junet@informix.com (June Tong) wrote: > Mark Truty (marktrut@ix.netcom.com) wrote: > : Is what I am trying to do impossible in DBACCESS?? > > : I have a file with two columns, (name, cus_no) > > : I have a table with 50 columns, three of which are ( name, > : cus_no,number) > : I want to update the rows in the table with a number if the name and > : cus_no are the same from the file. > > : I tried > > : create temp table mark > : (mname integer, > : mcus_no integer); > : load from file insert into mark; > > : update customer > : set number = 100 > : where > : name = mark.mname and > : cus_no = mark.mcus_no > > > Instead of the UPDATE statement you have above, try this: > > UPDATE customer > SET number = (SELECT number FROM mark > WHERE mark.mname = customer.name > AND mark.mcus_no = customer.cus_no) > WHERE EXISTS (SELECT * FROM mark > WHERE mark.mname = customer.name > AND mark.mcus_no = customer.cus_no) > > You have to use the EXISTS clause to avoid nulling out any rows in customer > which don't exist in mark. If you're sure that all the rows in customer have > a corresponding row in mark (or if the number would be NULL anyway), then you > can leave this off (which should speed up the UPDATE). I think that should be: UPDATE customer SET number = 100 WHERE EXISTS (SELECT NULL FROM mark WHERE mark.mname = customer.name AND mark.mcus_no = customer.cus_no); > You have to use the EXISTS clause to avoid nulling out any rows in customer > which don't exist in mark. If you're sure that all the rows in customer have > a corresponding row in mark (or if the number would be NULL anyway), then you > can leave this off (which should speed up the UPDATE). > > > June > > ---- June Tong Informix Software ---- > ---- Senior Consultant (415) 926-6140 ---- > ---- International Support junet@informix.com ---- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd or Pyramid Technology Inc --------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44(0)1252 373035 ---mmm----- Senior Database Consultant Fax : +44(0)1252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.