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?
LIMIT 1
means you're updating a random row as there is noORDER BY
.max
- is that a column name? There certainly aren't any parameters being passed to the SQL standardmax()
. Whether it's valid SQL after that is sorted is "no", but it's a start