CODEWITHSUNDEEP
mysqldatabaseselectsubquery
웃웃웃웃웃
웃웃웃웃웃

Reputation: 11984

Mysql subquery returns Subquery returns more than 1 row error

Say i am having a table and some values like the following.

  -----------------------------------------
 | col1    | col2 | col3  | col4  |  col5  |
  ---------|------|-------|-------|--------
 | 6171368 | 1    | TEST  | 12053 | 123456 |
  -----------------------------------------
 | 6171368 | 2    | ABCD  | QWERT |        |
  -----------------------------------------

What i want to do is i need to get value of col5 of 1 row if value of col5 is empty without using where conditon exclude where col2 = 2. When i tried with a query i am getting an error saying

1242 - Subquery returns more than 1 row

My query is

SELECT col1,col2,col3,col4, 
if (col5 IS NULL or col5 = '' ,
    (
        select col5 from table 
        where col2 = 1 
        group by col1
    ),'')  as col5

Upvotes: 0

Views: 2081

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You want a correlated subquery that gets a valid value of col5 for the row (assuming you have more than one row).

SELECT col1, col2, col3, col4, 
       (case when col5 IS NULL
             then (select col5
                   from table t2
                   where t2.col1 = t.col1 and
                         t2.col5 is not null
                   limit 1
                  )
        end) as col5
from table t;

Upvotes: 1

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

From error, we can try something like below:

SELECT col1,col2,col3,col4, 
if (col5 IS NULL or col5 = '' ,
    (
        select col5 from table 
        where col2 = 1 
        group by col1 limit 1
    ),'')  as col5

Upvotes: 0

Related Questions