Piyal Shuvro Piyal Shuvro - 8 months ago 59
MySQL Question

Update rows with where condition returning multiple values

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.

Answer Source

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');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download