MySQL更新有两个子查询 [英] MySQL update with two subqueries

问题描述

我正在尝试使用返回日期的子查询和WHERE子句的另一子查询来更新MySQL表的一列.

I'm trying to update one column of MySQL table with subquery that returns a date, and another subquery for the WHERE clause.

在这里:

UPDATE wtk_recur_subs_temp 
   SET wtk_recur_date = (SELECT final_bb.date
                           FROM final_bb, wtk_recur_subs 
                          WHERE final_bb.msisdn = wtk_recur_subs.wtk_recur_msisdn) 
WHERE wtk_recur_subs_temp.wtk_recur_msisdn IN (select final_bb.msisdn 
                                                 from final_bb)

MySQL引擎的响应是子查询返回多于1行".

The response from the MySQL engine is "Subquery returns more than 1 row".

推荐答案

这可能会给您带来极大的震撼，但是您的一个子查询返回的行多于一个！

This may come as an utter shock to you, but one of your subqueries is returning more than one row!

在您设置的情况下，这是不允许的.这两个子查询中的每一个必须返回一行且仅返回一行.还是没有行.

This isn't permitted in the circumstance you've set up. Each of those two subqueries must return one and only one row. Or no rows.

自行执行每个子查询，并确定哪个子查询返回多个行.如果他们不应该返回多于一行，那么您的数据可能是错误的.如果它们返回的行不只一行，那么您要么想要修改数据以使它们不(正如我假设的那样)，要么添加LIMIT子句.或在查询外部添加聚合函数(如MAX)，以对返回的多行做适当的处理.

Perform each subquery on it's own and determine which one is returning more than one row. If they shouldn't return more than one row, your data may be wrong. If they should return more than one row, you'll either want to modify the data so they don't (as I assume you expect), or add a LIMIT clause. Or add an aggregate function (like MAX) outside the query to do something proper with the multiple rows being returned.

这篇关于MySQL更新有两个子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！