How to avoid duplicate data in the subquery

Question

I have two tables as below.

Product table:

+-----+------------+-----+-------+--------+
| id  | activityId | age | queue | status |
+-----+------------+-----+-------+--------+
| 100 |          2 |   0 | start |      2 |
| 101 |          3 |   0 | in    |      5 |
+-----+------------+-----+-------+--------+

Department table:

+-----+------------+-------+----------+
| id  | activityId | queue | exittime |
+-----+------------+-------+----------+
| 100 |          1 | new   | null     |
| 100 |          2 | start | null     |
| 100 |          2 | start | null     |
| 101 |          1 | new   | null     |
| 101 |          1 | new   | null     |
| 101 |          3 | in    | null     |
| 101 |          3 | in    | null     |
+-----+------------+-------+----------+

I am trying to update product table age column with below query. But its returning error as ORA-01427 Single-row subquery returning more than one row.

     update Product pd set pd.age = (select (case when dp.exittime!= null then 
     (sysdate - dp.exittime)   
     else ( case when pd.queue = dp.queue 
     then (select (sysdate - dp1.entrytime) from department dp1 where pd.id = dp1.id 
      ) else 2 END) END)
     from department dp
     where dp.id > 1
     AND pd.id = dp.id
     AND pd.status in('1','7','2','5')
     AND pd.queue= dp.queue
     AND pd.activityId = dp.activityId ) 
     where exists 
     (select 1 from department dp
     where dp.id > 1
     AND pd.id = dp.id
     AND pd.status in('1','7','2','5')
     AND pd.queue= dp.queue
     AND pd.activityId = dp.activityId );

Subquery returning multiple values due to activityId in department table. How can I avoid sub-query returning multiple value.

What part of the error do you not understand? It seems quite clear. How to fix it depends on your data and what you want to do. — Gordon Linoff, Sep 08 '20 at 11:48
I want to update the **age** field of corresponding **Id** in **Product** table with the given conditions — saloni, Sep 08 '20 at 11:51
If you run your sub-query you'll see that it (at least *sometimes*) returns more than one row. Either fix the problem with the data (if there is one) so you alsways get one row returned, or add logic to determine how to handle multiple rows (average the values, sum them, take the first/last or minimum/maximum, etc)? — MatBailie, Sep 08 '20 at 11:51

score 3 · Answer 1 · answered Sep 08 '20 at 12:00

This query will identify the scenarios under which you get mutliple rows.

select
  dp.id,
  dp.queue,
  dp.activityId,
  COUNT(*)
from
  department   dp
inner join
  product      pd
    ON  pd.id = dp.id
    AND pd.queue= dp.queue
    AND pd.activityId = dp.activityId
where
     dp.id > 1
 AND pd.status in('1','7','2','5')
GROUP BY
  dp.id,
  dp.queue,
  dp.activityId
HAVING
  COUNT(*) > 1

For those cases you need to determine one of the following...

How to fix the data to return only one row
How to fix the query to return only one row
How to pick just one row from the multiple rows returned

As we can't see your data, we can't fix any of that for you.

After investigating, however, you may be able to return with a more specific question.

How to avoid duplicate data in the subquery

1 Answers1