Syntax on update with multiple joins

Question

I'm having an issue updating a table with a select using multiple joins. I feel like everything is in place but I'm getting some syntax problems around the end, as commented below.

    UPDATE ambition.ambition_totals a
    INNER JOIN (SELECT 
                  c.user AS UserID,
                  COUNT(*) AS dealers,
                  ROUND((al.NumberOfDealers / al.NumberOfDealerContacts) * 100 ,2)  AS percent
          FROM contact_events c
          JOIN users u
          ON c.user = u.id
          JOIN dealers d
          ON c.dealer_num = d.dealer_num
            LEFT JOIN (
              SELECT user_id, COUNT(*) AS NumberOfDealerContacts,
              SUM(CASE WHEN ( d.next_call_date + INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS NumberOfDealers
                  FROM attr_list AS al
                     JOIN dealers AS d ON d.csr = al.data
                       WHERE al.attr_id = 14
                       GROUP BY user_id)) as al
                     ON al.user_id = a.ext_id -- this is where I have a syntax error
      SET a.dealers_contacted = al.dealers,
        a.percent_up_to_date = al.percent;

As shown, I'm getting the data needed from these joins but I'm unable to update based on my ON clause in the final join. The select itself works apart from this, but I'm just trying to alter it to update a table.

I'm sure I'm just overlooking something in the syntax but I get an error that 'every derived table must have its own alias'.

UPDATE

Original working select that needs to be converted into the update:

    SELECT 
        c.user AS UserID,
        COUNT(*) AS Number_of_recorded_events,
        ROUND((al.NumberOfDealers / al.NumberOfDealerContacts) * 100 ,2)  AS Percentage_up_to_date
    FROM contact_events c
    JOIN users u
    ON c.user = u.id
    JOIN dealers d
    ON c.dealer_num = d.dealer_num
    LEFT JOIN (
      SELECT user_id, COUNT(*) AS NumberOfDealerContacts,
      SUM(CASE WHEN ( d.next_call_date + INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS NumberOfDealers
      FROM jackson_id.attr_list AS al
      JOIN jfi_dealers.dealers AS d ON d.csr = al.data
      WHERE al.attr_id = 14
      GROUP BY user_id) AS al
    ON al.user_id = c.user
    GROUP BY UserID;

Answer 1

'every derived table must have its own alias'

This error is pretty clear. A derived table is when you put a subquery in a FROM clause or JOIN clause, which you do twice in your query.

Every time you do this, you must give each of these derived table subqueries an alias, so you can reference columns returned by the subquery.

Like:

SELECT t.foo FROM (SELECT foo FROM MyTable) AS t

This must be done for every such subquery. In your case, you have something like this form:

UPDATE a 
INNER JOIN (
  SELECT ... FROM c JOIN u JOIN d 
  LEFT JOIN (SELECT ... FROM al JOIN d ...)
) AS al 
SET ...

You have one level of subquery, which you give the alias al.

But you don't give an alias for the innermost subquery, the one you did a LEFT JOIN on. That one needs an alias too.

P.S.: This question is actually a duplicate of What is the error "Every derived table must have its own alias" in MySQL? from 2009. I know Stack Overflow encourages us to close new questions as duplicates if there is already an old answer. But I also know the reality is that people tend not to search old posts much.

On the other hand, that old Stack Overflow post from 2009 is literally the first result when I google for the error string 'every derived table must have its own alias'.