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

Richard,

Pretty close, but you don't really need the join in the sub-query.  just
make the vendor field reference, and it will handle the constraint
correctly.

Also, for convenience and clarity, I always use table correlators to
simplify the coding of joins.  

>  update file1 a
>     set a.code = (select b.newcode
>                    from  file2 b
>                    where a.vend1 = b.vend2)

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Richard B Baird [mailto:rbaird@xxxxxxxxxxxxxxxxxxxxx]
Sent: Friday, March 28, 2003 11:53 AM
To: Midrange Systems Technical Discussion
Subject: While we're on the subject, a more complicated SQL question.



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

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.