使用sql查询交换两行

发布于 2024-11-07 20:36:08 字数 487 浏览 0 评论 0 原文

我有一个名为 ticket 的表，当用户从前端单击向上/向下按钮时，我想在其中交换两行。

为此，我添加了一个名为 Ticket_index 的自动生成字段。但我无法执行此操作。

我写了以下查询

   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

谁能给我正确的 SQL 查询？如果需要额外信息，请告诉我。谢谢

原文

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 following 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

著墨染雨君画夕 2024-11-14 20:36:08

使用 case 语句，例如：

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

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);

回复收藏 0 原文

孤凫 2024-11-14 20:36:08

既然您的 Ticket_index 字段是一个身份（自动递增整数）字段，那么您到底为什么要交换这些索引呢？根据您的问题，听起来您真正想要的是一种管理排序顺序的方法，它应该真正独立于索引/主键。我知道这不能回答您的问题，但我的建议是添加一个单独的列来控制排序顺序。

回复收藏 0 原文

开始看清了 2024-11-14 20:36:08

您确定只更新两行吗？

UPDATE ticket SET ticket_index = 4 WHERE ticket_id = 18

UPDATE ticket SET ticket_index = 5 WHERE ticket_id = 301

使用简短的事务来确保更新两条记录。

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.

回复收藏 0 原文

静若繁花 2024-11-14 20:36:08

假设：您有一个订单列（例如ticket_order）。

观察：向下移动 Ticket_order N 的工单与向上移动 Ticket_order N+1 的工单具有相同的效果，因此您只需要一个例程即可交换两个元素（无论是向上移动还是向下移动）。

在本例中，我们创建一个“下移例程”，它只需要一个参数：您想要下移的票据的顺序。但是，您的最终查询可能需要更多参数来唯一标识要交换订单的两张票。例如，您可能需要添加票证类别或其他内容。

此例程需要的（一个）查询是这样的（其中 [order] 是您想要向下移动的工单的顺序）：

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)

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)

回复收藏 0 原文

~没有更多了~