Home » SQL & PL/SQL » SQL & PL/SQL » update with subqueries possibilty
update with subqueries possibilty [message #675810] Mon, 22 April 2019 01:43 Go to next message
shahzad-ul-hasan
Messages: 561
Registered: August 2002
Senior Member
SQL> UPDATE STUDENT SET ADM_NO=
  2  (select RANK() Over(ORDER BY doa) AS ADM_NO FROM STUDENT
  3  where adm_clas not in ('SIX','SEVEN','EIGHT'))
  4  WHERE EXISTS
  5  (select RANK() Over(ORDER BY doa) AS ADM_NO FROM STUDENT
  6  where adm_clas not in ('SIX','SEVEN','EIGHT'));
(select RANK() Over(ORDER BY doa) AS ADM_NO FROM STUDENT
 *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row



SQL> UPDATE STUDENT SET ADM_NO=
  2  (select RANK() Over(ORDER BY doa) AS ADM_NO FROM STUDENT
  3  where adm_clas not in ('SIX','SEVEN','EIGHT'));
(select RANK() Over(ORDER BY doa) AS ADM_NO FROM STUDENT
 *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row


please sort out this query;
Re: update with subqueries possibilty [message #675814 is a reply to message #675810] Mon, 22 April 2019 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please feedback in your previous topics and post the solutions you found.
Please follow the forum rules.
Please stop acting as a parasite.

Re: update with subqueries possibilty [message #675826 is a reply to message #675810] Mon, 22 April 2019 06:08 Go to previous message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
It is possible, but storing rank makes no sense since it will change as soon as you insert, delete or update table.

SY.
Previous Topic: update collection of object type using update statement
Next Topic: optimizer_feature_enable Hint
Goto Forum:
  


Current Time: Fri Sep 18 03:50:08 CDT 2020