Subquery returning multiple rows during update

Mayur Joshi picture Mayur Joshi · Oct 3, 2014 · Viewed 9.8k times · Source

I have two tables T_SUBJECTS (subject_id, date_of_birth) and T_ADMISSIONS (visit_id, subject_id, date_of_admission, age). I want to update the age column with the age at time of admission. I wrote the update query and get the "single row sub-query returns more than one row". I understand the error but thought the where exists clause will solve the problem. Below is the query.

UPDATE
  t_admissions
SET
  t_admissions.age =
  (
    SELECT
      TRUNC(months_between(t_admissions.date_of_admission,
      t_subjects.date_of_birth)/12)
    FROM
      t_admissions,
      t_subjects
    WHERE
      t_admissions.subject_id           = t_subjects.subject_id
    AND t_admissions.age = 0
    AND t_admissions.date_of_admission IS NOT NULL
    AND t_subjects.date_of_birth       IS NOT NULL
  )
WHERE
   EXISTS
  (
    SELECT
      1
    FROM
      t_admissions, t_subjects
    WHERE
      t_admissions.subject_id = t_subjects.subject_id
  ); 

Answer

Mr. Llama picture Mr. Llama · Oct 3, 2014

The problem is that your subquery in the SET clause returns multiple rows.
Having a WHERE clause will only filter which records get updated and nothing else.
In addition, your where clause will either always return true or always return false.

You should look into how to properly do a correlated update:
https://stackoverflow.com/a/7031405/477563