如何使用过程更新列值 [英] how to update column values using procedure

问题描述

大家好.

请帮忙.

我有一张桌子如下...
Book_Issue
ID NO_OF_DAYS
1 15
2 13
3 7
4 2
5 12

我想更新每一行，这将从NO_OF_DAYS中扣除一天，这意味着每天数字都会扣除一.我做了类似的事情，但是它用相同的值更新了所有行.

Hello All.

Please help.

I have a table as below...
Book_Issue
ID NO_OF_DAYS
1 15
2 13
3 7
4 2
5 12

I want to update each row which will deduct one day from NO_OF_DAYS means every day the numbers will deduct by one. I did some thing like this but its updating all rows with same value.

Declare @loop as int,@Count as int,@Days as int
Set @Days = 0
Set @Count = 0
Set @loop = 0
Select @Count = Count(*) from book_issue_Detail
While(@loop!=@Count)
Begin
Select @Days = no_of_Days from book_issue_detail
update book_issue_detail set no_of_days = @Days - 1
set @loop = @loop + 1
End

推荐答案

您可以直接运行更新查询而无需任何循环

you can directly run the update query without any loop

update book_issue_detail set no_of_days = no_of_days - 1

上面的查询将更新所有记录并从no_of_days中扣除1天

Above query will update all records and deduct 1 day from no_of_days

嗨Asghar，

这不是一种有效的设计方式.请注册其发布日期，并通过计算任意日期和发布日期之间的天数，您可以动态计算NO_OF_DAYS.

但是，这里的操作方式是:如果由于某些技术问题，有一天忘记更新该列，将会发生什么情况.如果一天中多次更新该行会发生什么.

如果您更改了设计，请使用此函数来计算您的NO_OF_DAYS:
DATEDIFF [获取 [ ^ ]

希望对您有所帮助.

Hi Asghar,

This is not an efficient way of design. Please register their issue date and by computing the DAYs between any date and issue date you can compute the NO_OF_DAYS dynamically.

But about the way you have done it here : What happens if you forget to update the column someday due to some technical problems. And what happens if you update that row multiple times in a day.

If you changed your design use this function to have your NO_OF_DAYS computed :
DATEDIFF[^] and for getting today date : GETDATE[^]

Hope it helps.

这篇关于如何使用过程更新列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！