Home > DeveloperSection > Forums > Mysql: Sub-query update on same table?
Ida B Wells
Ida B Wells

Total Post:30

Posted on    July-09-2013 3:11 AM


 1 Reply(s)
 1673  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



Total Post:604

Posted on    July-09-2013 4:40 AM

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;

Don't want to miss updates? Please click the below button!

Follow MindStick