CODEWITHSUNDEEP
mysql
Abbas Soliman
Abbas Soliman

Reputation: 35

MySQL - (#1242) Sub-query returns more than 1 row

I have tbl which include 2 columns: title and params, the values are like the following:

{ "ufield926":"34", "ufield927":"Sud", "ufield928":"Ara", "ufield929":"Mecca", "ufield930":"1/1/1983", "ufield933":"011", "ufield934":"Mub", "ufield943":"SU/HI/14", "ufield944":"Average", "ufield946":"Female" } I want to extract the code after "ufield943": which is SU/HI/14 only and concatenate it with the value in title column to be like the following:

--> Teaching (SU/HI/14)

Here is the query I have tried:

SELECT CONCAT(title, "(", (select 
       substring(
         params,
         locate('ufield943', params) + 12,
         locate('ufield944', params) - locate('ufield943', params) - 21
       ) FROM tbl), ")") AS title
FROM tbl;

I get the following error everytime I run the query

"#1242 - Subquery returns more than 1 row"

Upvotes: 2

Views: 135

Answers (1)

Alberto Moro
Alberto Moro

Reputation: 1013

I remove {"..."} from title with the SUBSTRING and CHAR_LENGTH functions together.

I change - 21 to - 16 and add space before (

SELECT CONCAT(substring(title, 3,CHAR_LENGTH(title) - 4 ), " (", 
   substring(
     params,
     locate('ufield943', params) + 12,
     locate('ufield944', params) - locate('ufield943', params) - 16
   ), ")") AS Title
FROM tbl;

DEMO

Upvotes: 1

Related Questions