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

Question

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

title: {"Teaching"}
params:

{ "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"

Alberto Moro · Accepted Answer · 2019-07-08T07:33:24.177

1

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

edited Jul 08 '19 at 07:33

answered Jul 03 '19 at 12:14

Alberto Moro

1,014
11
22

It's working with single row, however when tried to run the query from "phpmyadmin" to apply the changes to all rows the mentioned error pops-up – Abbas Soliman Jul 03 '19 at 12:29
The query works fine but get unintended results get "Teaching (SUV)" in all rows instead of "Teaching (SU/HI/14)" !! – Abbas Soliman Jul 04 '19 at 11:41
Thank you it works. Another question in case I want to update title column to have the code I have grabbed form params to be like "Teaching (SU/HI/14)" with space and with only "/", what I could modify the mentioned query to get that? – Abbas Soliman Jul 07 '19 at 10:54
1

There is absolutely no reason to use a subquery if both the title and params field are in the same table – Matteo Tassinari Jul 08 '19 at 07:23
1

@AbbasSoliman For the space change `"("` to `" ("`. For change title that's not possible in pure SQL. You'll probably have to query the data out and then process it in whatever application is executing the query. – Alberto Moro Jul 08 '19 at 07:26
@MatteoTassinari thank you, i didn't see this big mistake. I change my response. – Alberto Moro Jul 08 '19 at 07:34
@Alberto Moro could I add "update" with the working query to do so? if yes what could the query look like? – Abbas Soliman Jul 08 '19 at 08:42
1

@AbbasSoliman You can try something like `UPDATE tbl SET title = CONCAT(...)`. For a more precise question I would like to know the structure of the table, some data you have and some data you want. Try doing something yourself then if you can't, you can open a new question – Alberto Moro Jul 08 '19 at 09:35
@Alberto Moro Thanks it works like charm, but wanted to replace "\/" to be "/" with the following query: "update tbl set title = replace(title,'\/','/')" and get this answer: "0 rows affected" !! – Abbas Soliman Jul 08 '19 at 11:59
1

@AbbasSoliman I try this and work fine [demo](https://www.db-fiddle.com/f/cAdntrK2Q5Gu1gbn8TUPFk/0). Take care when using `\\` beacuse is mysql special character. If you can help [read here](https://stackoverflow.com/questions/881194/how-do-i-escape-special-characters-in-mysql) – Alberto Moro Jul 08 '19 at 12:22

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

1 Answers1