问题描述
我正在尝试根据同一表中不同列中的值更新 sql server 2014 中的表列.这是表格的片段.
I'm trying to update a table column in sql server 2014 based on values in a different column from the same table. Here is a snippet of the table.
CREATE TABLE [dbo].[Table1](
[id] [int] NULL,
[number] [varchar](50) NULL,
[fruit] [varchar](50) NULL
INSERT [dbo].[Table1] ([id], [number], [fruit]) VALUES (1, NULL, N'one')
INSERT [dbo].[Table1] ([id], [number], [fruit]) VALUES (2, NULL, N'apple')
INSERT [dbo].[Table1] ([id], [number], [fruit]) VALUES (3, NULL, N'banana')
INSERT [dbo].[Table1] ([id], [number], [fruit]) VALUES (4, NULL, N'orange')
INSERT [dbo].[Table1] ([id], [number], [fruit]) VALUES (5, NULL, N'two')
INSERT [dbo].[Table1] ([id], [number], [fruit]) VALUES (6, NULL, N'apple')
INSERT [dbo].[Table1] ([id], [number], [fruit]) VALUES (7, NULL, N'banana')
+----+--------+--------+
| id | number | fruit |
+----+--------+--------+
| 1 | NULL | one |
| 2 | NULL | apple |
| 3 | NULL | banana |
| 4 | NULL | orange |
| 5 | NULL | two |
| 6 | NULL | apple |
| 7 | NULL | banana |
+----+--------+--------+
+----+--------+--------+
| id | number | fruit |
+----+--------+--------+
| 1 | one | one |
| 2 | one | apple |
| 3 | one | banana |
| 4 | one | orange |
| 5 | two | two |
| 6 | two | apple |
| 7 | two | banana |
+----+--------+--------+
基本上我想用水果列中的值更新数字中的空列,每当出现不是苹果、香蕉、橙"的字符串时,我希望继续输入该值,直到出现新字符串水果.所以结果应该像第二个例子.
Basically I want to update the null columns in number with values from the fruit column, whenever a string appears that isn't 'apple, banana, orange' and I want that value to keep being entered until a new string appears in fruit. So the outcome should look like the second example.
我想我需要使用某种循环,但我不确定如何去做,到目前为止我的尝试是
I think I need to use a loop of some kind but I'm not really sure how to go about it, so far my attempt is
declare @i varchar
set @i = 'one'
while @i = 'one' or @i not in ('apple', 'banana', 'orange')
begin
update Table1
set number = @i
set @i = fruit
end
但是我在尝试设置 @i =fruit 时出错
But I get an error when trying to set @i = fruit
非常感谢任何帮助
推荐答案
declare @tb as TABLE ( [id] [int] NULL, [number] [varchar](50) NULL, [fruit] [varchar](50) NULL)
INSERT @tb ([id], [number], [fruit]) VALUES (1, NULL, N'one')
INSERT @tb ([id], [number], [fruit]) VALUES (2, NULL, N'apple')
INSERT @tb ([id], [number], [fruit]) VALUES (3, NULL, N'banana')
INSERT @tb ([id], [number], [fruit]) VALUES (4, NULL, N'orange')
INSERT @tb ([id], [number], [fruit]) VALUES (5, NULL, N'two')
INSERT @tb ([id], [number], [fruit]) VALUES (6, NULL, N'apple')
INSERT @tb ([id], [number], [fruit]) VALUES (7, NULL, N'banana')
select * from @tb
declare @count int = (select COUNT(*) from @tb)
declare @cursor int = 1
declare @updateValue as nvarchar(10)
While @cursor <= @count
BEGIN
set @updateValue = ISNULL((select fruit from @tb where id = @cursor and fruit not in ('apple', 'banana', 'orange')),@updateValue);
update @tb set number = @updateValue where id = @cursor
set @cursor = @cursor + 1;
END
select * from @tb
试试这个.
这篇关于根据另一列的值更新 sqlserver 中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!