UPDATE语句根据数字公式重新分配列值 [英] UPDATE statement to re-assign a column value according to a numeric formula

问题描述

给出一个具有如下值的表:

Given a table that would have values like this:

name, sortval, dept

bob,   2,       1
tom,   4,       1
mary,  6,       1
sam,   8,       1
tim,  10,       1

sortvalue决定了我输出列表的顺序，因此

The sortvalue determines the order in which I output the list so a

select name from table order by sortval ASC

看起来像这样:

bob
tom
mary
sam
tim

现在让我们说我想将Sam移到bob之后2行.我执行以下语句:

Now let's say I want sam to be moved up 2 rows after bob. I execute this statement:

UPDATE table set sortval=sortval-5 where name='sam'

现在表格如下:

name, sortval, dept

bob,   2,       1
tom,   4,       1
mary,  6,       1
sam,   3,       1
tim,  10,       1

这是一个问题....

我希望最简单的语句对sortval值重新排序，使它们再次成为，所有从2开始的偶数都按sortval的顺序递增2，因此最终会像这样:

I want the simplest statement to re-order the sortval values so they are once again, all even numbers starting with 2 going up incrementally by 2, in the order of sortval so it would end up like this:

name, sortval, dept

bob,   2,       1
sam,   4,       1
tom,   6,       1
mary,  8,       1
tim,  10,       1

我认为有一种简单的方法可以进行某种复合UPDATE，从而可以如上所述重置sortval值.

I figure there is a simple way to have some sort of compound UPDATE that can reset the sortval values as above.

我只是在寻找一个非常具体的UPDATE语句，该语句可以根据rownum * 2的简单公式以sortval的顺序重新分配sortval的值，并且想知道是否使用ORDER BY sortval会引起任何问题吗?

I'm simply looking for a very specific UPDATE statement that can re-assign the value of sortval according to a simple formula of rownum*2 in the order of sortval, and wanting to know if messing with sortval in a statement using ORDER BY sortval would cause any problem?

推荐答案

CASE语句可能会有所帮助.在此示例中:

A CASE statement may help. In this example:

• source(山姆)在位置8
• target bob在位置2

• the source, sam, is at position 8
• the target, bob, is at position 2

将变量替换为实际值，以下语句将所有内容从源向下移动2，将成员之间的位置保持不变，将目标设置为与源相等，将其余部分向下移动:

Replacing the variables with the actual values, the following statement shifts everything down 2 from the source, leaves the between members as is, sets the target equal to source, moves the rest down:

postgres=> SELECT * FROM test order by sortval;
 name | sortval
------+---------
 bob  |       2
 tom  |       4
 mary |       6
 sam  |       8
 tim  |      10
(5 rows)


postgres=>      UPDATE test
postgres->        SET sortval = CASE WHEN sortval <= 2 THEN sortval - 2
postgres->                           WHEN sortval = 8  THEN 2
postgres->                           WHEN sortval >= 8 THEN sortval - 2
postgres->                           ELSE sortval
postgres->                           END;
UPDATE 5
postgres=> SELECT * FROM test order by sortval;
 name | sortval
------+---------
 bob  |       0
 sam  |       2
 tom  |       4
 mary |       6
 tim  |       8
(5 rows)

那会使列表上移一些.类似的逻辑可以应用于下移列表.并且假设负数很好，并且只需要相对排序即可.

That would move something up the list. Similar logic could be applied for moving down a list. And it assumes negative numbers are fine an that just the relative ordering is of interest.

这篇关于UPDATE语句根据数字公式重新分配列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！