mysql - SQL查询错误，需要一些帮助

我有一个查询，应该会退还人们过去一周购买的一半东西：

UPDATE main_data SET md.amount_current = md.amount_current + (bought.total / 2)
FROM main_data AS md
INNER JOIN (
    SELECT DISTINCT sb.user_id, SUM(sb.spend) AS total
    FROM shopitems_bought AS sb
    LEFT JOIN shopitems AS si
    ON sb.shopitem_id = si.id
    WHERE sb.date_bought <= '2012-03-09'
    AND sb.date_bought > DATE_ADD('2012-03-09', INTERVAL -7 DAY)
    AND si.valid = 1
    GROUP BY sb.user_id
) AS bought ON bought.user_id = md.user_id
WHERE md.valid = 1

SELECT部分自己执行得很好，并返回正确的数据（应该退还的id以及他们在那一周的花费）。但是，查询作为一个整体抛出一个错误，说我在第2行附近的SQL语法中有一个错误（我引用：“FROM main_data a s md INNER JOIN（SELECT DISTINCT sb.forum_id，SUM（sb.s）”）。
我看不出我做错了什么。

最佳答案

MySql uses a different syntax for join with update statements比你在上面用的还要多。尝试将查询更改为：

UPDATE main_data md
INNER JOIN (
    SELECT DISTINCT sb.user_id, SUM(sb.spend) AS total
    FROM shopitems_bought AS sb
    LEFT JOIN shopitems AS si
    ON sb.shopitem_id = si.id
    WHERE sb.date_bought <= '2012-03-09'
    AND sb.date_bought > DATE_ADD('2012-03-09', INTERVAL -7 DAY)
    AND si.valid = 1
    GROUP BY sb.user_id
) bought ON bought.user_id = md.user_id
SET amount_current = md.amount_current - (bought.total / 2)
WHERE md.valid = 1

注意，我变了

SET amount_current = md.amount_current + (bought.total / 2)

减去而不是加：

SET amount_current = md.amount_current - (bought.total / 2)

关于mysql - SQL查询错误，需要一些帮助，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9711294/