phpmyadmin Question posted in
The official documentation can be found here.

Why will this SQL code work in Microsoft Access database but not in phpMyAdmin? How should i change it to work correctly?

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.

  1. An expression was expected. (near "(" at position 7)
  2. Unexpected token. (near "(" at position 7)
  3. A new statement was found, but no delimiter between it and the previous one. (near "SELECT" at position 8)
  4. Unexpected token. (near ")" at position 102)
  5. 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:

VolunteersForTable

ConventionTable
Relationship between the two tables

Here is the before and after of using the newly suggested query from the user akina:

before: after:

The desired result should be to have the total funding column in the after image to read 0, 160, 1250, 1250

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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.

    UPDATE VolunteersFor 
INNER JOIN Convention ON VolunteersFor.location = Convention.location
SET Convention.total_funding = Convention.total_funding + VolunteersFor.donationAmount;

    UPDATE VolunteersFor 
INNER JOIN Convention ON VolunteersFor.location = Convention.location
SET VolunteersFor.donationAmount = 0;

  2. 0 
    UPDATE Convention 
INNER JOIN VolunteersFor ON VolunteersFor.location = Convention.location
SET Convention.total_funding = Convention.total_funding + VolunteersFor.donationAmount, 
    VolunteersFor.donationAmount = 0;

    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

    Login or Signup to reply.
  3. 0

    #1248 – Every derived table must have its own alias

    So, try that:

    UPDATE 
    (SELECT * 
    FROM VolunteersFor 
    INNER JOIN Convention 
    ON VolunteersFor.location = Convention.location) AS T
SET 
    T.total_funding = T.total_funding + T.donationAmount, 
    T.donationAmount = 0;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search