Re: RE: ROWNUM - Can the inner-outer idea be extended to an update?
next tested it but id guess. dont know about 8i. why would you want to do that? if you post what your goal is, ther emay be a better method. UPDATE EMP2 SETGENDER = 'F' WHERE GENDER = (Select gender.(nested stuff) here. same as from that link. From: MaryAnn Atkinson [EMAIL PROTECTED] Date: 2003/07/09 Wed PM 01:34:24 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: ROWNUM - Can the inner-outer idea be extended to an update? Can the inner-outer idea be extended to an update? UPDATE EMP2 SETGENDER = 'F' WHERE GENDER = ' ' ANDROWNUM BETWEEN 10 AND 20; That does NOT work, because as we explained the returned rows are numbered starting from 1, and so rownum never really matches a 10 or 20 or anything in between. Thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Rownum
Darrell Landrum,ÄúºÃ£¡ thanx for your explaination. I wonder :whether i can judge the select return. I find the rowNUM is not 0, is right? === 2003-03-19 05:03:00 ÄúÔÚÀ´ÐÅÖÐдµÀ£º=== Santosh, Good question. Actually these 2 are not related. Rowid is an actual attribute assigned to each row of a table and is unique. Rownum is logically assigned to rows returned in a query (nothing to do with the table). It is assigned sequentially as rows are returned (meaning if you can't have a rownum 2 until you've gotten a rownum 1). Ex. SQL select rowid from dual; ROWID -- CsAABGkAAA SQL select rownum from dual; ROWNUM -- 1 SQL select rownum from dual where rownum=2; no rows selected SQL select rownum from dual where rownum 2; ROWNUM -- 1 Hope this was helpful, Darrell [EMAIL PROTECTED] 03/19/03 03:43AM Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = Ö Àñ£¡ Áõ¼Ì¶« [EMAIL PROTECTED] 2003-03-19 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?GB2312?Q?=C1=F5=BC=CC=B6=AB?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re: Rownum
That's correct, there's no rownum of 0. ex. SQL select rownum, username from dba_users; ROWNUM USERNAME -- -- 1 SYS 2 SYSTEM 3 OUTLN 4 OEM_DLAND 5 DBSNMP 6 HR 7 PM 8 DLANDRUM 9 OE 10 WMSYS 11 ORDSYS 12 ORDPLUGINS 13 MDSYS 14 CTXSYS 15 QS_CS 16 QS_CB 17 QS_CBADM 18 QS_OS 19 QS_ES 20 QS_WS 21 QS 22 QS_ADM 23 SH 24 XDB 25 ANONYMOUS 26 WKSYS 27 WKPROXY 28 ODM 29 ODM_MTR 30 OLAPSYS 30 rows selected. I'm not sure what you mean by 'judge the select return', but the only real use I can think of for rownum is to restrict your query to a small random sampling of your data... ex. SQL select table_name, tablespace_name from dba_tables 2 where rownum 5; TABLE_NAME TABLESPACE_NAME -- -- SEG$ SYSTEM CLU$ SYSTEM OBJ$ SYSTEM FILE$ SYSTEM [EMAIL PROTECTED] 03/19/03 08:33AM Darrell Landrum,-? +úí thanx for your explaination. I wonder :whether i can judge the select return. I find the rowNUM is not 0, is right? === 2003-03-19 05:03:00 -?+++ -++-- +ú === Santosh, Good question. Actually these 2 are not related. Rowid is an actual attribute assigned to each row of a table and is unique. Rownum is logically assigned to rows returned in a query (nothing to do with the table). It is assigned sequentially as rows are returned (meaning if you can't have a rownum 2 until you've gotten a rownum 1). Ex. SQL select rowid from dual; ROWID -- CsAABGkAAA SQL select rownum from dual; ROWNUM -- 1 SQL select rownum from dual where rownum=2; no rows selected SQL select rownum from dual where rownum 2; ROWNUM -- 1 Hope this was helpful, Darrell [EMAIL PROTECTED] 03/19/03 03:43AM Hello list, I wanted to know the concept of Rownum.. Is it similar to Row Id ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = +- +±úí -)+ ½ [EMAIL PROTECTED] 2003-03-19 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?GB2312?Q?=C1=F5=BC=CC=B6=AB?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).