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?