mysql - DELETE FROM with LIMIT 不起作用 - 错误 1064

Question

基本上我有这个查询

SELECT *
FROM   `transactions` a
WHERE  Round(( tender1 - `change` ), 2) != (SELECT Round(Sum(total), 2)
                                            FROM   `transactions` b
                                            WHERE  b.trans_no = a.trans_no)
       AND tender1 != 0
LIMIT  1 

这很好并返回一行，但如果我将语句更改为删除

DELETE FROM `transactions` a
WHERE  Round(( tender1 - `change` ), 2) != (SELECT Round(Sum(total), 2)
                                            FROM   `transactions` b
                                            WHERE  b.trans_no = a.trans_no)
       AND tender1 != 0
LIMIT 1  

这会返回错误 1064 语法无效

有什么想法吗？？？

Answer 1

我不确定为什么你有LIMIT，但我认为如果你想删除任意行，这会起作用:

DELETE FROM `transactions` WHERE id IN (
    SELECT id FROM `transactions` a 
    WHERE ROUND((TENDER1 - `CHANGE`),2) != (SELECT ROUND(SUM(TOTAL),2) 
                                            FROM  `transactions` b 
                                            WHERE b.trans_no = a.TRANS_NO) 
    AND TENDER1 != 0 
    LIMIT 1
)