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