根据另一列的值更新 sqlserver 中的列 [英] Updating column in sqlserver based on values from another column

问题描述

我正在尝试根据同一表中不同列中的值更新 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 中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！