My query is:
UPDATE (SELECT *
FROM VolunteersFor
INNER JOIN Convention ON VolunteersFor.location = Convention.location)
SET Convention.total_funding = Convention.total_funding + VolunteersFor.donationAmount,
VolunteersFor.donationAmount = 0;
this code is supposed to add a volunteer’s donation to a convention when they are in the same location as the convention, but in phpMyAdmin I get these errors:
static analysis:
5 errors were found during analysis.
- An expression was expected. (near "(" at position 7)
- Unexpected token. (near "(" at position 7)
- A new statement was found, but no delimiter between it and the previous one. (near "SELECT" at position 8)
- Unexpected token. (near ")" at position 102)
- A new statement was found, but no delimiter between it and the previous one. (near "SET" at position 106)
MySQL said: Documentation
#1248 – Every derived table must have its own alias
I don’t understand does phpMyAdmin not allow the use of parenthesis? How am I supposed to write a subquery without them?
Here are the images of the tables and their relationship for your reference:
Here is the before and after of using the newly suggested query from the user akina:
The desired result should be to have the total funding column in the after image to read 0, 160, 1250, 1250
3
Answers
If the query gets spit into two parts like this: this will ensure that the donation amount will be added to the total funding BEFORE it gets set to zero.
The relation
VolunteersFor.location = Convention.location
must guarantee strict (one row)-to-(one row) joining, if not then the result is non-deterministric, and it must be wrong.If not then you must provide full info (DDLs, sample data, desired result).
DEMO
So, try that: