Skip to Main Content
  • Questions
  • difference of update with rownum without rownum

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, santhosh rreddy.

Asked: September 15, 2016 - 7:52 am UTC

Last updated: May 23, 2017 - 1:24 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi tom,

I have some doubts with below update statement

1. UPDATE emp SET empno = rownum;
2. UPDATE emp SET empno = 1;


question no 1--

first one executing with out errors and populating column with unique values while second one populating only 1 in all records.
how update statement handles multiple rownum values in 1 statement.?

question no 2--

could you tell me how update statement works with WHERE clause and without WHERE clause(dont tell me that with WHERE it updates only single row ,without where it update all rows,i wanna know what goes inside)?

and Chris said...

1. Oracle assigns rownum as it processes each row. Each new row increments the value for rownum. This is why the first update gets unique values for each row.

With the second update you're setting everything to 1!

2. See http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 21, 2017 - 4:39 am UTC

Here i confused with one sentence
"Oracle assigns rownum as it processes each row."
Here Processing row means Getting Data From Data base(Physical Memory device) to Cache (SGA or simply RAM) or Displaying Data From Cache(SGA) to Client System?
Connor McDonald
May 23, 2017 - 1:24 am UTC

As a row is deemed "eligible", ie, it satisfies all the predicates and would be returned to a caller if fetched, it is *then* we assign the rownum.

That is why

select * from table where sal > 0 and rownum = 2

would never return rows, because we would do (conceptually)

row1:
- is sal > 0 ? yes, assign rownum = 1, ie, our first row that meets the criteria
- does it meet "rownum = 2", no, discard

row2:
- is sal > 0 ? yes, assign rownum = 1, ie, our first row that meets the criteria
- does it meet "rownum = 2", no, discard

row3:
- is sal > 0 ? yes, assign rownum = 1, ie, our first row that meets the criteria
- does it meet "rownum = 2", no, discard

etc etc