Subquery returning multiple rows during update

Question

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
  );

What would you like to happen when there are multiple hits? Just pick any value of the available ones? Ignore the row with duplicates entirely? — Joachim Isaksson, Oct 03 '14 at 16:17
I cannot ignore multiple hits. A same subject might have multiple admissions with different date of admission so age of admission will be different and I need to update those records. — Mayur Joshi, Oct 03 '14 at 17:50

score 1 · Accepted Answer · edited May 23 '17 at 11:54

1

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

edited May 23 '17 at 11:54

Community

1
1

answered Oct 03 '14 at 16:20

Mr. Llama

20,202
2
62
115

score 1 · Answer 2 · answered Oct 03 '14 at 18:16

A correlated update is what I need as suggested in the above link. See answer below.

UPDATE
  (
    SELECT
      t_admissions.visit_id,
      t_admissions.date_of_admission doa,
      t_admissions.age age,
      t_subjects.date_of_birth dob
    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
  )
SET
  age = TRUNC(months_between(doa,dob)/12);

Subquery returning multiple rows during update

2 Answers2