Mysql: Sub-query update on same table?

Total Post:30


 2378  View(s)
Rate this:
Hi Expert!

I can see why this is problematic, but can't seem to work out the fix like others here on SO have..

I wan't to increment the ticket number using a sub_query. Can't use auto_inc as already have an auto_inc'ing primary key..

Here's what i want to do:

UPDATE tickets SET tickets.ticket_number=(
    SELECT (MAX(ticket_number)+1) FROM tickets
) WHERE ticket_id=12345;

Gives me: You can't specify target table 'tickets' for update in FROM clause

  1. Re: Mysql: Sub-query update on same table?

    Hi Ida!

    Correct me if I'm wrong, but is it a result you wanted to achieve?:

    UPDATE tickets SET tickets.ticket_number=(
       SELECT max_ticket FROM (
       SELECT (MAX(ticket_number)+1) AS max_ticket FROM tickets ) AS sub_table) 
    WHERE ticket_id=12345;


Please check, If you want to make this post sponsored

You are not a Sponsored Member. Click Here to Subscribe the Membership.