Products

Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller

Developer

Article Blog Forum Interview Beginner News Startup Quiz

Users

Career

Apply Job Internship

Pricing

Login Sign Up Get a Quote

Mysql: Sub-query update on same table?

Anonymous User 3795 09 Jul 2013

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!

Ask by Anonymous User

Updated 09 Jul 2013

1 Answers

AVADHESH PATEL 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;

MindStick Training

Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.