Re: DBACCESS Impossibility?????

Warning: It's highly unlikely that you want to do what is suggested by Nick. See below. > On 20 Dec 1995, Mark Truty wrote: > > } Is what I am trying to do impossible in DBACCESS?? No > } > } 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 > } > } This does not work. It says no table named mark in query. Any > } suggestions????? > nnob@loc.gov (Nick Nobbe) writes: > Mark, > You're missing the mark table name in the where. Try something > like this - > > update customer > set number = 100 > where name in > (select mname from mark ) > and cus_no in > (select mcus_no from mark ) ; > > Hope this gets the wheels turning, > Nick The above statement will update the number field for every customer where the name occurs in some record of the mark-table and cus_no occurs in any record in mark, *also* another one than where the name occurs. The two sub-selects are completely independent, the and between them notwithstanding. If your customer table contains: name cus_no number aaa 1 0 bbb 2 0 ccc 3 0 and the loaded mark table contains: mname mcus_no aaa 1 bbb 3 ddd 2 customer.number would be updated to 100 not only in the record containing name = aaa and cus_no = 1, but also the one with name = bbb and cus_no = 2 using the above update statement. I am not even sure if the following will allways work: update customer set number = 100 where name in (select mname from mark where mcus_no = cus_no) but it does update the above example correctly at least. Normally I would do something like this in 4GL in a loop to be sure that it could do nothing but what I wanted. That may also be faster as subselects tends to be slow. Nils.Myklebust@ccmail.telemax.no NM-data, Aasesvei 71, 1300 Sandvika, Norway My opinions are those of my company