MYSQL: SUB-QUERY UPDATE ON SAME TABLE?

Ida B Wells

Total Post:30

Points:210
Posted by  Ida B Wells
MYSQL 
 1727  View(s)
Ratings:
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

Thanks!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    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;

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!