Thread: SQL Quries
10-18-12, 00:29 #1Registered User
- Join Date
- Oct 2012
Unanswered: SQL QuriesCode:
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] 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
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.
10-18-12, 11:01 #2Registered User
Provided Answers: 1
- Join Date
- Aug 2003
- Where the Surf Meets the Turf @Del Mar, CA
>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.
10-18-12, 12:24 #3Registered User
- Join Date
- Mar 2007
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?