MySQL 查询错误"Subquery returns more than 1 row"更新到同一个表中

Question

如何更新到同一个表？我试图进行分数转换并更新同一表中的1行，但是错误"0"；子查询返回多于1行的"；

下面是代码，(State1是表本身，Share字段由那些分数数据组成，如1/2,1/5..(

UPDATE State1 set shareresult =(SELECT
x.Multiplier / x.Divider as Result
from
(select 
cast( substr( t.share, 
1, 
locate('/', t.share) - 1) 
as decimal) 
as Multiplier,
cast( substr( t.Share, 
locate('/', t.Share) + 1, 
locate( ' ', 
concat(t.Share, ' '))) 
as decimal) 
as Divider
from
State1 t ) x) 

感谢

最后，我设法用下面的代码解决了我的问题

CREATE TEMPORARY TABLE activity_product_ids AS SELECT x.NumberID, x.Multiplier / x.Divider as Result from (select cast( substr( t.share, 1, locate('/', t.share) - 1) as decimal) as Multiplier, cast( substr( t.Share, locate('/', t.Share) + 1, locate( ' ', concat(t.Share, ' '))) as decimal) as Divider, t.ID as numberID from State1 t) x;
UPDATE State1 a
JOIN activity_product_ids b
ON a.ID=b.NumberID
SET a.shareresult=b.Result;

谢谢你的回复。

Answer 1

您的更新语句和子查询都没有WHERE子句，所以看起来您正试图通过每行的小数转换来更新每一行。

这是一种简单得多的方法-

UPDATE State1
SET shareresult = ROUND(SUBSTRING_INDEX(share, '/', 1) / SUBSTRING_INDEX(share, '/', -1), 2)
WHERE id = 1;