Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE with Order By
UPDATE with Order By [message #256157] Fri, 03 August 2007 03:58 Go to next message
kapilk
Messages: 6
Registered: June 2007
Location: delhi
Junior Member

UPDATE cust_orders cor
SET cor.batchid = seq_cor_batch_id,
cor.cor_wm_filename = vcCorWmFilename
WHERE ROWNUM < nMaxRowsToReturn
ORDER BY cor.cor_created_date;

I dont want to order them into a select statement and then update.
Is there is anyway to do that???

[Updated on: Fri, 03 August 2007 07:33] by Moderator

Report message to a moderator

Re: Please help UPDATE with Order By [message #256161 is a reply to message #256157] Fri, 03 August 2007 04:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That doesn't make sense.

Duplicate thread (OTN forums)

MHE

[Updated on: Fri, 03 August 2007 04:13]

Report message to a moderator

Re: URGENT!!! Please help UPDATE with Order By [message #256162 is a reply to message #256157] Fri, 03 August 2007 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing urgent in forum but YOU to read read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

For instance,
SQL> alter table emp add (rownumber integer);

Table altered.

SQL> update emp a
  2  set rownumber = (select rn 
  3                   from (select rowid row_id,
  4                                row_number () over (order by sal desc) rn
  5                         from emp) b
  6                   where b.row_id = a.rowid
  7                  )
  8  /

9 rows updated.

SQL> select sal, rownumber from emp order by 2;
       SAL  ROWNUMBER
---------- ----------
      2450          1
      1600          2
      1500          3
      1300          4
      1250          5
      1250          6
      1100          7
       950          8
       800          9

9 rows selected.

SQL> rollback;

Rollback complete.

SQL> merge into emp a
  2  using (select row_number () over (order by sal desc) rn from emp) b
  3  on (b.rowid=a.rowid)
  4  when matched then update set rownumber=b.rn
  5  /

9 rows merged.

SQL> select sal, rownumber from emp order by 2;
       SAL  ROWNUMBER
---------- ----------
      2450          1
      1600          2
      1500          3
      1300          4
      1250          5
      1250          6
      1100          7
       950          8
       800          9

9 rows selected.

Regards
Michel


Regards
Michel
Re: URGENT!!! Please help UPDATE with Order By [message #256163 is a reply to message #256162] Fri, 03 August 2007 04:22 Go to previous messageGo to next message
kapilk
Messages: 6
Registered: June 2007
Location: delhi
Junior Member

Hi Michel,

Is that answer to my query???
Re: URGENT!!! Please help UPDATE with Order By [message #256168 is a reply to message #256163] Fri, 03 August 2007 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes!

Regards
Michel
Re: UPDATE with Order By [message #256176 is a reply to message #256157] Fri, 03 August 2007 05:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think what you're trying to do is to update the Cust_Orders table, and set some fields on the nMaxRowsToReturn least recent records, as ordered by the Cor_Created_Date

If you want to do it with an UPDATE, you can do it like this:
drop table jer_temp;

create table jer_Temp (col_1  varchar2(10), create_Date  date);

insert into jer_temp (select 'A'||level, sysdate-level from dual connect by level <=20);

update jer_Temp
set    col_1 = 'B'
where  rowid in (select rowid
                 from  (select rowid
                              ,row_number() over (order by create_date) rnum
                        from   jer_temp)
                 where rnum <= 10);


Michael has shown you the basics of a more elegant (and more performant) approach using MERGE, which you could expland like this:
drop table jer_temp;

create table jer_Temp (col_1  varchar2(10), create_Date  date);

insert into jer_temp (select 'A'||level, sysdate-level from dual connect by level <=20);

merge into jer_Temp a
using (select col_1
             ,create_date 
       from (select col_1
                   ,create_Date
                   ,row_number() over (order by create_date) rnum 
             from jer_temp) 
       where rnum <= 10) b
on (a.rowid = b.rowid)
when matched then update set col_1 = 'B';

Previous Topic: Mutating error
Next Topic: group by clause
Goto Forum:
  


Current Time: Fri Dec 09 21:25:12 CST 2016

Total time taken to generate the page: 0.10564 seconds