CODEWITHSUNDEEP
mysql
Haroldo
Haroldo

Reputation: 37377

Mysql: Sub-query update on same table?

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 incement 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!

Upvotes: 1

Views: 4286

Answers (3)

Ami
Ami

Reputation: 397

    update tickets
    set ticket_number= (
       select (MAX(ticket_number)+1) from (
          select * from ticket
       ) as x)
    where ticket_id=12345;

Upvotes: 0

Fuv
Fuv

Reputation: 922

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;

Upvotes: 6

Brian Hoover
Brian Hoover

Reputation: 7991

I've run across this before. In mySQL, you have to create another layer of abstraction when trying to update a table with values from the same table.

UPDATE tickets SET ticket_number=(
  select t from (  
  SELECT (MAX(ticket_number)+1) t FROM tickets t1) as t2
) WHERE ticket_id=12345;

Link to Demo

Upvotes: 1

Related Questions