T-SQL 从其他行号更新记录
在 SQL Server 2008 中,我有 table1 并且想要更新表,如第二个表所示。 即从上面的记录更新 Rn(2) 的更新值 1 = Rn(1) 的值 2。顺序由Rn决定。 在这方面的任何帮助将不胜感激。
非常感谢。
Create table Table1(ID int, value1 int, value2 int, Item varchar(10),Rn int)
insert into Table1(ID,Value2,Item,Rn) values('1','33242','Orange','1')
insert into Table1(ID,Value2,Item,Rn) values('2','67665','Orange','2')
insert into Table1(ID,Value2,Item,Rn) values('3','78765','Orange','3')
insert into Table1(ID,Value2,Item,Rn) values('4','576','Orange','4')
insert into Table1(ID,Value2,Item,Rn) values('5','906658','Orange','5')
insert into Table1(ID,Value2,Item,Rn) values('6','54435','Orange','6')
insert into Table1(ID,Value2,Item,Rn) values('7','7464','Mango','1')
insert into Table1(ID,Value2,Item,Rn) values('8','9876','Mango','2')
insert into Table1(ID,Value2,Item,Rn) values('9','2433','Mango','3')
insert into Table1(ID,Value2,Item,Rn) values('10','5654','Mango','4')
insert into Table1(ID,Value2,Item,Rn) values('11','13213','Mango','5')
insert into Table1(ID,Value2,Item,Rn) values('12','9867867','Mango','6')
insert into Table1(ID,Value2,Item,Rn) values('13','5644355','Mango','7')
insert into Table1(ID,Value2,Item,Rn) values('14','6534','Apple','1')
insert into Table1(ID,Value2,Item,Rn) values('15','343','Apple','2')
insert into Table1(ID,Value2,Item,Rn) values('16','423','Apple','3')
insert into Table1(ID,Value2,Item,Rn) values('17','7666','Apple','4')
In SQL Server 2008, I have table1 and would like to update table as shown in second table.
i.e. Update update Value1 of Rn(2) = Value2 of Rn(1) from above record. Sequence is decided by Rn.
Any help in this regard will be appreciated.
Many Thanks.
Create table Table1(ID int, value1 int, value2 int, Item varchar(10),Rn int)
insert into Table1(ID,Value2,Item,Rn) values('1','33242','Orange','1')
insert into Table1(ID,Value2,Item,Rn) values('2','67665','Orange','2')
insert into Table1(ID,Value2,Item,Rn) values('3','78765','Orange','3')
insert into Table1(ID,Value2,Item,Rn) values('4','576','Orange','4')
insert into Table1(ID,Value2,Item,Rn) values('5','906658','Orange','5')
insert into Table1(ID,Value2,Item,Rn) values('6','54435','Orange','6')
insert into Table1(ID,Value2,Item,Rn) values('7','7464','Mango','1')
insert into Table1(ID,Value2,Item,Rn) values('8','9876','Mango','2')
insert into Table1(ID,Value2,Item,Rn) values('9','2433','Mango','3')
insert into Table1(ID,Value2,Item,Rn) values('10','5654','Mango','4')
insert into Table1(ID,Value2,Item,Rn) values('11','13213','Mango','5')
insert into Table1(ID,Value2,Item,Rn) values('12','9867867','Mango','6')
insert into Table1(ID,Value2,Item,Rn) values('13','5644355','Mango','7')
insert into Table1(ID,Value2,Item,Rn) values('14','6534','Apple','1')
insert into Table1(ID,Value2,Item,Rn) values('15','343','Apple','2')
insert into Table1(ID,Value2,Item,Rn) values('16','423','Apple','3')
insert into Table1(ID,Value2,Item,Rn) values('17','7666','Apple','4')
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
这会将每个“块”的第一行的 Value1 设置为 null。您可以使用 ISNULL 来覆盖它并将其设置为 0,或任何其他可能合适的默认值。
This sets Value1 of the first row of each "block" to null. You could use ISNULL to override this and make it a 0, or whatever other default value may be appropriate.
可能会很困难,因为您还没有定义任何父子关系。您可以在插入时执行此操作(然后可能定义关系...)吗?
等等...
Might be difficult since you haven't defined any parent child relationships. Can you just do it at insert time (and possibly define the relationship then...)?
and so on...
我一直觉得这是一个令人痛心的事情。但我建议使用光标。像这样的东西; (这只是骨架,不起作用!但是你应该能看出原理)
DECLARE @field1 integer
声明 @fiedl2 整数
声明 @NewValue 整数
SET @NewValue = 0
声明 Tab1 CURSOR FOR
从 Table1 中选择 *
从 Tab1 中获取下一个
进入@field1、@field2,
同时@@FETCH_STATUS = 0
开始
结束
关闭 Tab1
解除分配 Tab1
I've always found this to be a right pain in the bum. But I would suggest a cursor. Something like this; (This is just the skeleton. It doesn't work! But you should be able to see the principle)
DECLARE @field1 integer
DECLARE @fiedl2 integer
DECLARE @NewValue integer
SET @NewValue = 0
DECLARE Tab1 CURSOR FOR
SELECT * FROM Table1
FETCH NEXT FROM Tab1
INTO @field1, @field2
WHILE @@FETCH_STATUS = 0
BEGIN
END
CLOSE Tab1
DEALLOCATE Tab1
我的脑海中浮现出类似这样的内容:
update table1 t1 set t1.value1 = (select t2.value2 from table1 t2 where t2.id = t1.id + 1) where t1.rn != 1
Off the top of my head, something like:
update table1 t1 set t1.value1 = (select t2.value2 from table1 t2 where t2.id = t1.id + 1) where t1.rn != 1