根据另一列中的值更新sqlserver中的列

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.

我正在尝试根据同一个表中不同列中的值更新sql server 2014中的表列。这是表格的一小部分。

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.

基本上我想用水果列中的值更新数字中的空列，只要出现不是'apple，banana，orange'的字符串，我希望在水果中出现新字符串之前一直输入该值。所以结果应该看起来像第二个例子。

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

But I get an error when trying to set @i = fruit

但是在尝试设置@i = fruit时出错

Any help is greatly appreciated

任何帮助是极大的赞赏

2 个解决方案

#1

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

Try this.

尝试这个。

#2

UPDATE dbo.Table1
SET number = t.fruit
FROM (SELECT t1.id, t2.fruit    
      FROM (SELECT id, 
                   MAX(CASE WHEN fruit NOT IN ('apple','banana','orange') 
                            THEN id ELSE 0 END)
                   OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS i
           FROM dbo.Table1) t1
      INNER JOIN dbo.Table1 t2 ON t2.id = t1.i) t
WHERE t.id = dbo.Table1.id;

The MAX expression uses newer (Sql Server 2012) windowing functions to pair an id with the id of the closest preceding row not containing 'apple', 'banana', or 'orange', then it just joins to the original table and does the update.

MAX表达式使用较新的（Sql Server 2012）窗口函数将id与最近的前一行的id配对，不包含'apple'，'banana'或'orange'，然后它只是连接到原始表并执行更新。