Re: Limit UPDATE statements to affect only one row

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 11 Aug 2006 13:31:16 -0400
Message-ID: <4k3t56Fajs0cU1@individual.net>

DA Morgan wrote:
> Serge Rielau wrote:

>> And, no, I don't have Oracle installed any more than you have IDS 
>> installed. At least my contributions are constructive. :-)

> You don't think I have IDS installed ... On what basis?
> This is a university ... we have everything installed.
> If someone sold it, gave it away, or lost it ... we have it.
> And so do I. ;-)

And this is IBM. Premier Oracle partner IBM ;-)

UPDATE emp SET sal = 1 WHERE rownum <= 1 => Works!

UPDATE (SELECT sal FROM emp WHERE rownum <= 1) SET sal = 1 => Doesn't work

UPDATE (SELECT ROW_NUMBER() OVER() AS rn, sal FROM emp)

   SET sal = 1 WHERE rn = 1
=> Wants ORDER BY in the OVER clause, then decides not to work.

Oh well
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Received on Fri Aug 11 2006 - 12:31:16 CDT