Update rows with where condition returning multiple values

Question

Consider the following two tables

Table A

ID | Name | Price | Size

1    aaa    10      L

2    bbb    12      L

3    ccc    15      L

4    ddd    20      XL

Table B

ID | Type 

1     X

2     X

3     Y

4     Z

Now I want to update the price attribute of table A by 1% where the size is L and the Type is X. I wrote this update statement

UPDATE A SET price =  price * 1.01

WHERE size = 'L' AND id = (SELECT id FROM B WHERE type = 'X');

But this gives me

ORA-01427: single-row subquery returns more than one row error.

I know that the problem is in "id = (SELECT id FROM B WHERE type = 'X');" because it is giving multiple values.

Give me some idea about how can I resolve this issue.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

This select returns 2 rows

SELECT id FROM B WHERE type = 'X'

Result

ID | Type
1    X
2    X

Thus, use IN if you compare value to select that returns multiple rows

UPDATE A 
  SET price =  price * 1.01
 WHERE size = 'L' 
   AND id IN (SELECT id FROM B WHERE type = 'X');

Update rows with where condition returning multiple values

Sign up