1

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?

3
  • 2
    I don't know where to start with this. LIMIT 1 means you're updating a random row as there is no ORDER BY. max - is that a column name? There certainly aren't any parameters being passed to the SQL standard max(). Whether it's valid SQL after that is sorted is "no", but it's a start
    – Philᵀᴹ
    May 20, 2014 at 1:27
  • Provide in simple English what the update is supposed to do. Tables with sample rows, before and after the update would help. You can use SQLfiddle.com May 20, 2014 at 8:24
  • I have updated the question and reorderd the statement and named colum max maxtickets so its more clear.
    – Sjoerd
    May 20, 2014 at 8:28

1 Answer 1

2

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 ;
1
  • Thank you this works perfectly and its a great example for future problems, I will start playing with joins alot more to improve my sql skills. Marked this as the answer. For study purposes ill rewrite my original insert. My project is unstuck again! thnx
    – Sjoerd
    May 20, 2014 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.