RE: While we're on the subject, a more complicated SQL question.

["Richard B Baird" <rbaird@xxxxxxxxxxxxxxxxxxxxx>]

Vern, Walden, Dave, Eric,

my pre-meeting planning meeting is over, and all we agreed on was to meet
again next week. <SIGH>....

thanks for the 'point me the right way' eva'body.  Dave was closest, but
the winner is.....  ME!!!!

For the archives - this is how to do it:
------
update file1
   set code = (select newcode
                 from file2
                where file1.vend1 = file1.vend2)
 where vend1 in (select vend2 from file2)
------
this assumes no duplicate vendors in file2.  it will bomb if there are.

the 'outer' (1st level select, last line) WHERE clause keeps the query from
trying to assign null to a code in file1 where there is no associated
vendor record in file2.  the last select provides a list of valid (IN)
vendors to update.

And Joe, I agree.  I LOVE this kind of ad hoc stuff you can do with SQL,
even though it would have taken me all of about 3 minutes to knock off an
RPG program to do the same thing - and this lasted the better part of an
afternoon (in duration, not in actual time worked on ;)

i'd done it before a long time ago, but forgot how.  hopefully i'll
remember next time, and at least now it'll be in the archives.

thanks again all

rick

-----my original question was..... ---------------
all this SQL talk, and I come up with a doozie.

can you do a UPDATE to one file based on the contents of another file?
Something like this:

file1  (has all vendors)
 vend1
 code
 other
 stuff

file2  (only has selected vendors, not all)
 vend2
 newcode

now, I'm just guessing, to give you the idea of what I'm trying to do:

  update file1
     set code = (select newcode
                   from file1,
                        file2
                  where vend1 = vend2)

am I on the right track, or am I just p***ing in the wind?

thanks,

rick