Re: Limit UPDATE statements to affect only one row

Serge Rielau wrote:
> 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. ;-)

For enough money my mother could be a Premier Partner too. ;-)

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

So does:
UPDATE emp SET sal = 1 WHERE rownum = 1; which was my point. Less than or equal to has not context as less than is impossible. You can not update zero rows.

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

I often find using a valid syntax improves things in Oracle. Perhaps that isn't required with DB2.

UPDATE (
   SELECT sal
   FROM emp)
SET sal = 1
WHERE rownum = 1;

> Oh well
> Serge

Oh well indeed. ;-)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org