Results 1 to 3 of 3

Thread: SQL Quries

  1. #1
    Join Date
    Oct 2012
    Posts
    5

    Unanswered: SQL Quries

    Code:
    DECLARE
    	     CURSOR ARROW IS
             SELECT * FROM SHANGHAIRANKING
    	          FOR UPDATE OF RANKING;
    	  BEGIN
    	       FOR NUM IN ARROW
    	        LOOP
    	           UPDATE SHANGHAIRANKING
    	             SET = count(RANKING)
    	            WHERE  RANKING = '51' and RANKING ='76';
    	        END LOOP;
    	  END;
    	   /
    Error message is DBMS_OUTPUT:
    ------------


    >[Error] Script lines: 1-13 -------------------------
    ORA-06550: line 9, column 18:
    PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification
    ORA-06550: line 8, column 12:
    PL/SQL: SQL Statement ignored
    Script line 9, statement line 9, column 18

    [Executed: 10/17/2012 10:19:28 PM] [Execution: 0ms]


    Can someone possible able help or direct me to right place to find out more information about using update with cursor. Basically, I need to remove any duplicate from RANKING columns like there's is a possible of 25 universities that have 51 ranking and 10 universities that have 76 ranking. I need to remove the duplicate and ranking column should have 1 to 100 count down. If someone possible provide feed back


    Example
    xample would be

    RANKING UNAME SCORE
    1 Stanford University 78.6
    2 Cornell University 77.5
    2 UCLA University 76.0
    3 New York University 74.6
    4 Princeton University 73.5
    5 Ohio University 71.0
    5 YALE University 70.0

    So basically, here i have change the RANKING column with series number instead of duplicate as you see
    Last edited by Deve; 10-18-12 at 00:33.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >SET = count(RANKING)
    which column get assigned this new value?

    > WHERE RANKING = '51' and RANKING ='76'
    how can a single row have ranking = '51' AND ranking = '76'?
    consider changing "AND" to "OR"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    I am quite puzzled what is your exact requirement as your text description does not match the example below at all.

    Of course it would be more understable, if you provided a testcase - CREATE TABLE statements for table structure, INSERT statements for sample data, expected result set and exact rules for achieving it.

    It seems you could benefit from using Oracle aggregate functions, especially ROW_NUMBER, RANK or DENSE_RANK. They are described in SQL Language Reference book, which is available with other documentation e.g. online on http://tahiti.oracle.com/

    Is a row uniquely identified by UNAME?
    What shall be final RANKING when two UNAMEs have the same SCORE?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •