子查询使用select语句返回超过1行的更新查询解决方案

问题描述：

您好，我有查询，其中我已使用select语句编写了更新语句.但是不幸的是，子查询返回错误会返回1行以上的错误.我知道错误即将到来.但我不知道相同的解决方案.谢谢. 这是查询:

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

答

在对SET configuration=(SELECT ...)使用update时，子查询必须返回不超过一个值(一行).如果返回多个值，如何将两行表分配给标量configuration字段.因此，您应该弄清楚为什么子查询返回多于一行的内容，并修复该子查询，或者确定多于一行的情况下选择哪个ONE值来进行更新.例如，您可以选择最大值

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)...

或任何一个第一个值

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

以此类推...

如果需要将所有行连接起来并放入一行中configureation，则可以使用

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 ....

子查询使用select语句返回超过1行的更新查询解决方案

相关推荐