how to update column values using procedure

Question

0.00/5 (No votes)

See more:

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.

SQL

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

Posted 22-Dec-11 0:03am

Asghar Sajjad

Updated 22-Dec-11 0:04am

Karthik Harve

v2

Add a Solution

Comments

Karthik Harve 22-Dec-11 6:05am

[Edit] pre tags added.

2 solutions

Solution 2

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.

Posted 22-Dec-11 6:25am

Amir Mahfoozi

Comments

Asghar Sajjad 23-Dec-11 1:04am

Thanks for your reply, as you pointed out i will think about it but i want to create a job which i will schedule for every day, my idea was if the user doesn't open an application then the function which is doing this job will not be executed.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Om Prakash Pant · Accepted Answer · 2011-12-22T00:25:00

Solution 1

you can directly run the update query without any loop

SQL

update book_issue_detail set no_of_days = no_of_days - 1

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

Posted 22-Dec-11 0:25am

Om Prakash Pant

Comments

AmitGajjar 22-Dec-11 7:52am

5 for you buddy...

Asghar Sajjad 23-Dec-11 1:16am

Thanks Prakash, you save my day, thanks a lot.