子查询使用select语句返回多于1行的更新查询解决方案 [英] Subquery returns more than 1 row solution for update query using select statement

问题描述

您好，我有一个查询，其中我使用 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

推荐答案

当你使用 update 和 SET configuration=(SELECT ...) 子查询必须返回不超过一个值(一个排).如果它返回多个值，您如何将两行表分配给例如标量 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 您可以使用 GROUP_CONCAT() mysql函数:

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行的更新查询解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！