Mysql – Error #1093 you-cant-specify-target-table-table-name-for-update-in-from-clause

errorsMySQL

I'm trying the following sql code:

UPDATE ticketsorders
SET amount = $val 
WHERE id = $ticketsorder->id
  AND ((SELECT COALESCE(SUM(amount), 0) 
        FROM ticketsorders 
        WHERE tickettype_id = $tickettype->id) + $val ) 
      <= (SELECT maxtickets 
          from tickettypes 
          where id = $tickettype->id)
  AND ((SELECT COALESCE(SUM(amount), 0) 
        FROM ticketsorders 
        WHERE event_id = $eventid) + $val ) 
      <= (SELECT maxtickets from events where id = $eventid)

$val = the number of tickets
there is a maximum number defined in maxtickets for an event and for a tickettype thats what the 2 ANDS are for. I have a similair insert statement which works.

But I keep getting:

#1093 – You can't specify target table 'ticketorders' for update in FROM clause.

I have placed alot of as x as y etc to try and make it work like in examples https://stackoverflow.com/questions/8333376/you-cant-specify-target-table-table-name-for-update-in-from-clause

The wheres are there to make sure the update only succeeds when the new sum is below a set maxtickets on both event and tickettype.

Can anyone enlighten me?

Best Answer

I do not like the method provided in the linked question, due to the way UPDATE is processed in MySQL.

Especially in your case, where you want to allow (or restrict) the update based on values from several rows of the table (and not just the row to be updated), I suggest you use 2 statements, one to get the max and sum values from the tickettypes, events and ticketsorders tables and then update accordingly or use JOIN if you want to do this in one statement. The CROSS JOIN are not a problem here as the three first subqueries produce one row each: 

UPDATE 
    ( SELECT maxtickets 
      FROM tickettypes 
      WHERE id = $tickettype->id
    ) AS tt
  CROSS JOIN 
    ( SELECT maxtickets 
      FROM events 
      WHERE id = $eventid
    ) AS ev
  CROSS JOIN
    ( SELECT COALESCE(SUM(amount), 0) AS sum_amount
      FROM ticketsorders 
      WHERE tickettype_id = $tickettype->id
    ) AS ts
  JOIN
    ticketsorders AS t
      ON  ts.sum_amount +$val <= tt.maxtickets
      AND ts.sum_amount +$val <= ev.maxtickets  
SET 
    t.amount = $val 
WHERE 
    t.id = $ticketsorder->id ;
Related Question