Mysql subquery returns Subquery returns more than 1 row error

Question

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

Gordon Linoff · Accepted Answer · 2014-01-24 12:05:02Z

1

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;

answered Jan 24, 2014 at 12:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suresh Kamrushi · Accepted Answer · 2014-01-24 11:56:24Z

0

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

answered Jan 24, 2014 at 11:56

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

Collectives™ on Stack Overflow

Mysql subquery returns Subquery returns more than 1 row error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related