Re: Tricky SQL update

["Michael Naughton" <michael_naughton@xxxxxxxxxxxx>]

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.