Mysql: Sub-query update on same table?

Question

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!

What are you trying to do? Do you want to update one row, or *every* row? — gen_Eric, Jul 08 '13 at 15:14
i deleted my answer, it will work but we need to know what you are trying to do... i think there's a better way... — fthiella, Jul 08 '13 at 15:18
Just like the error says - you cannot update a table from which you're reading. — Marc B, Jul 08 '13 at 15:32
I can see solutions for other people's similar questions however no one is giving a blueprint style answer, instead including the OP's huge queries so its hard to see what's actually fixed it! — Haroldo, Jul 08 '13 at 15:39
Just curious: why don't you make life easier and use the primary key for the ticket number? — Curt, Jul 08 '13 at 15:58
i have a ticket_id for each ticket and then a "ticket_number" for each ticket at each event.. — Haroldo, Jul 08 '13 at 16:02

Fuv · Accepted Answer · 2013-07-08T15:56:22.313

6

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;

edited Jul 08 '13 at 15:56

answered Jul 08 '13 at 15:47

Fuv

922
2
12
25

score 1 · Answer 2 · answered Jul 08 '13 at 15:52

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

score 0 · Answer 3 · answered Oct 07 '14 at 05:50

0

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

answered Oct 07 '14 at 05:50

Ami

397
2
4
19

Mysql: Sub-query update on same table?

3 Answers3