0

My students table has the following tables

student id | student year | test result | semester

I would like to group the records together to see how many re-tests did the student do in a particular semester.

I am trying to alter the table and add the total_tests_taken column to the table and use an update statement like:

ALTER table students
(add  total_tests_taken number );

UPDATE students 
SET total_tests_taken = (select count(*)  OVER ( PARTITION BY student_id, semester) FROM students)

but my sql fails saying: "ORA-01427: single-row subquery returns more than one row"

what am I doing wrong?

Do I need to create a temp table and than do it?

Thanks

Simon
  • 1,416
  • 1
  • 15
  • 24
Mysimple Nae
  • 1

1 Answers1

0

the reason you are getting the error is because you are trying to set a column's value = a table. SET statement would update each row that matches the constraint with the given value. What you are trying to do can be accomplished by UPDATE with JOIN statement if your DBMS supports it. you can check out the answer to this question for the syntax

How can I do an UPDATE statement with JOIN in SQL?

Community
  • 1
  • 1
Steve
  • 11,696
  • 7
  • 43
  • 81
  • Can I join a table with itself? There's only 1 table I am working with. Also, I need to count how many records exist for the student Id/semester combination, so I do need a count or a over/partition – Mysimple Nae Nov 07 '14 at 22:16
  • @MysimpleNae sure, you can definitely join with itself. – Steve Nov 07 '14 at 22:30