Re: Suggestions on a good way to write an Update with a join

> Let's say I want to copy all rows whose
> PKRevision column is 5 to the corresponding rows
> with PKRevision equal to 0. In Sybase, I could
> write the query this way:
>
> UPDATE Fu
> SET FuCol4 = f2.FuCol4
> , FuCol5 = f2.FuCol5
> , FuCol6 = f2.FuCol6
> FROM Fu f2
> WHERE Fu.PKFu1 = f2.PKFu1
> AND Fu.PKFu2 = f2.PKFu2
> AND f2.PKRevision = 5
> AND f2.PKRevision = 0

This should do what you want:

UPDATE fu fu2
SET (fucol4,fucol5,fucol6) = (SELECT fucol4,fucol5,fucol6 FROM fu WHERE pkfu1 = fu2.pkfu1 AND pkfu2 = fu2.pkfu2 AND pkrevision = 5) WHERE pkrevision = 0
AND EXISTS (select 1 from fu WHERE pkfu1 = fu2.pkfu1 AND pkfu2 = fu2.pkfu2 AND pkrevision = 5) ;

David J. Hazledine
Roche Products Limited
40 Broadwater Road
Welwyn Garden City
AL7 3AY
United Kingdom
Registration Number 100674

E-mail: david.hazledine_at_roche.com
Fax: +44 (0)1707 325666
Tel: +44 (0)1707 366166
--
Posted via Talkway - http://www.talkway.com Exchange ideas on practically anything (tm). Received on Wed May 19 1999 - 05:50:13 CDT