如何根据SQL中的列的新值设置列的值? [英] how to set column's value according to new value of a column in sql?

问题描述

我有一个表格名称:颜色.

颜色:
ID(主键，身份)|职位|颜色名称
              nbsp; bsp                | nbrsp; |
            1   b                nbsp; b >             2   b                 b >             4   nbsp; b                 b >             5   nbsp; bsp                nbsp; b >             8   b                nbsp; b >
现在，当我要插入以"2"作为位置和"Yellow"作为Color_Name的行时.

我必须重置表所有行的位置值，插入新行(2，黄色)，并在表中需要以下结果.

ID(主键，身份)|职位|颜色名称
              nbsp; bsp                | nbrsp; |
            1   b                nbsp; b >             2   b                 b >             4   nbsp; b                b >             5   nbsp; bsp                nbsp; b >             8   b                nbsp; b >             9   nbsp; b                nbsp; b >
如何在存储过程中做到这一点?或告诉我使用SQL语句执行此操作的步骤.

在此先感谢您.

I have a table name: Colors.

Colors:
ID(PrimaryKey,Identy)| Position | Color_Name
                             |            |
               1                  1               Red
               2                  2               Black
               4                  3               Blue
               5                  5               Purple
               8                  6               Pink

Now, when i going to insert a row having ''2'' as Position and ''Yellow'' as Color_Name.

I have to reset position value of all rows of a table,insert the new row(2,Yellow) and need the following result in table.

ID(PrimaryKey,Identy)| Position | Color_Name
                             |            |
               1                  1               Red
               2                  3               Black
               4                  4               Blue
               5                  5               Purple
               8                  6               Pink
               9                  2               Yellow

How can i do this in a store procedure? or tell me the steps with SQL-statements to do this.

Thanks in advance.

推荐答案

IMO，您的逻辑流程如下-

确定新颜色的位置.
浏览列表以查看该位置是否已被另一种颜色占据.
如果没有，那么容易-只需在此位置插入记录即可.
如果是，则更新位置大于或等于该位置(将位置递增1)的所有颜色的行.
现在插入当前颜色.


祝您好运！

IMO, your logic flow would be something as follows -

Decide the position of the new color.
Scan through the list to see if that position is already taken by another color.
If no, then easy - just insert your record with this position.
If yes, then update the rows of all colors where position is greater than or equal to this position (incrementing position by 1).
Insert the current color now.


Good luck!

在SP中遵循以下步骤:

1.首先找到它已经存在的位置2
脚本:从位置= 2的颜色中选择*；
插入Colors(position，color_name)values(2，Yellow);
如果找到，则
运行此脚本:update Colors设置position = position + 1;
其他
运行此脚本:将其插入Colors(position，color_name)values(2，Yellow);
结束


现在再次运行此脚本:从Colors中选择*；您将看到td行.

in SP follow below steps:

1.first find its already existed having postion 2
script : select * from Colors where position=2;
insert into Colors (position,color_name) values(2,Yellow);
if find then
run this script : update Colors set position=position+1;
else
run this script: insert into Colors (position,color_name) values(2,Yellow);
end


now run this script again: select * from Colors; you will see td row .

这篇关于如何根据SQL中的列的新值设置列的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！