I believe that would leave C2 set to its original profession, so you'd have to add a second statement something like this:

UPDATE file o SET Profession='' WHERE
NOT EXISTS (SELECT * FROM file i WHERE i.Client=o.Client AND i.ProductType='PT1')

You might be able to combine everything into a CASE statement if you really have to do it in one shot.

Of course, make a backup copy of your file before you try any of this :-)


Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:
Something simple...

UPDATE file o SET Profession = (
SELECT Profession FROM file i WHERE
i.Client = o.Client AND i.ProductType='PT1')
WHERE ProductType <> 'PT1'


The last WHERE isn't _needed_ but it avoids the redundant update that
set's PT1's profession to PT1's profession.

-Walden


Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-863-4357 x444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].