March 14, 2012
SQL query error, need some assistance
Question by Clavus
I’ve got this query that should refund people half the stuff they bought in the past week:
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
The SELECT part executes just fine on its own and returns the right data (IDs that should be refunded and how much they spend in that week). However the query as a whole throws an error saying I have an error in my SQL syntax near line 2 (I quote: ‘FROM main_data AS md INNER JOIN ( SELECT DISTINCT sb.forum_id, SUM(sb.s’).
I can’t see what I’m doing wrong.
Answer by Michael Fredrickson
MySql uses a different syntax for join with update statements than what you’re using above. Try changing your query to:
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
Note, I changed
SET amount_current = md.amount_current + (bought.total / 2)
To subtract instead of add:
SET amount_current = md.amount_current - (bought.total / 2)
Answer by Starx
There is no user_Id inside the INNER JOIN
UPDATE main_data SET md.amount_current = md.amount_current + (bought.total / 2)
FROM main_data AS md
INNER JOIN (
SELECT sb.user_id, DISTINCT sb.forum_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
