Swap two rows using sql query

I have a table called ticket in which I want to swap two rows when user click up/down button from front end.

For this I added a auto-generated field called ticket_index. But I am not able to perform this action.

I wrote the followi开发者_高级运维ng query

   UPDATE ticket as ticket1 
   JOIN ticket as ticket2 ON (ticket1.ticket_index = 1 AND ticket2.ticket_index = 4) 
   OR (ticket1.ticket_index = 4 AND ticket2.ticket_index = 1) 
   SET 
       ticket1.ticket_index = ticket2.ticket_index,
       ticket2.ticket_index = ticket1.ticket_index

Can anyone give me the right SQL query? Please let me know if extra information is needed for this. Thanks

---

use a case statement, e.g.:

update ticket
set ticket_index = case when ticket_index = :x then :y else :x end
where ticket_index in (:x, :y);

---

Since your ticket_index field is an identity (auto-incrementing integer) field, why in the world would you want to swap these indices? Based on your question, it sounds like what you're really looking for is a way to manage sort order, which should really be independent of the index/primary key. I know this doesn't answer your question, but my recommendation would be to add a separate column to control sort order.

---

Surely you just update the two rows?

UPDATE ticket SET ticket_index = 4 WHERE ticket_id = 18

UPDATE ticket SET ticket_index = 5 WHERE ticket_id = 301

Use a brief transaction to ensure you update both records.

---

Assumption: you have an order column (e.g. ticket_order).

Observation: moving a ticket with ticket_order N down has the same effect as moving a ticket with ticket_order N+1 up, therefore you only need one routine to swap two elements (whether it is moving up or down).

In this case we create a "move down routine", which only needs one parameter: the order of the ticket you want to move down. Your final query, however, might need more arguments to uniquely identify the two tickets for which you want to swap the order. For example, you might need to add a ticket category or whatever.

The (one) query this routine needs is thus (where [order] is the order of the ticket you want to move down):

UPDATE tickets
SET ticket_order = 2 * [order] - ticket_order + 1
WHERE ticket_order IN ([order], [order]+1)
(and possibly additional arguments here to further identify the tickets)