当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Updating column in sqlserver based on values from another column

问题描述:

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

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

Any help is greatly appreciated

网友答案:
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.

网友答案:
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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: