Subquery returns more than 1 row solution for update query using select statement

Question

Hello i have query in which i have written update statement using select statement. But unfortunately getting errors subquery returns more than 1 row. I know where the error is coming. But i dont know solution for the same.Thank you. Here is the query:

UPDATE adsetest.dashboard_widget_users
SET configuration=
  (SELECT DISTINCT ad_news_texte.headline
   FROM autodo.ad_news_texte
   INNER JOIN autodo.ad_news_oe
     ON ad_news_texte.news_id = ad_news_oe.id_ad_news
   INNER JOIN autodo.ad_news
     ON ad_news_oe.id_ad_news = ad_news.id
   WHERE ad_news.datum_archiv BETWEEN
     curdate() - INTERVAL DAYOFWEEK(curdate()) + 28 DAY AND curdate())
WHERE dsnr_yw_user = 1 AND dsnr_dashboard_widget = 1

You could always use LIMIT 1 in your subquery to avoid the error, but you need to fix your subquery to return only the row you want. — Marcus Adams

valex valex · Accepted Answer · 2014-06-24T12:52:41

When you use update with SET configuration=(SELECT ...) the subquery has to return no more than one value (one row). If it returns more than one value how do you assign two rows table for example to scalar configuration field. So you should figure out WHY your subquery returns more than one row and fix the subquery or decide which ONE value to select for update in case of more than one row. For example you can select maximum value

SELECT MAX(ad_news_texte.headline)...

or any one first value

(SELECT ad_news_texte.headline)... LIMIT 1)

and so on...

If you need to concatenate all rows and put it into one row configureation you can use GROUP_CONCAT() mysql function:

SET configuration=(SELECT GROUP_CONCAT(DISTINCT ad_news_texte.headline) FROM ....

Subquery returns more than 1 row solution for update query using select statement

2 Answers