forum

Home / DeveloperSection / Forums / Mysql: Sub-query update on same table?

Mysql: Sub-query update on same table?

Anonymous User302909-Jul-2013

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!

Updated on 09-Jul-2013

I am a content writter !

Can you answer this question?

Answer

09-Jul-2013

Hi Ida!

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

UPDATE tickets SET tickets.ticket_number=(

SELECT maticket FROM (

SELECT (MAX(ticket_number)+1) AS maticket FROM tickets ) AS sub_table)

WHERE ticket_id=12345;