How to select and perform query on a single column value of all the rows in a table o

[arsheena.alam]

Hi,

I have a table QuestionAsked which contains a column DateofAsking. Now what i need is to extract each date value and check whether the difference between that day and the current day is 14 or not. If yes than i have to update a particular column in that table.I am running the following query.

declare @d int
select @d=Datediff(dd,DateofAsking,getdate()) from QuestionAsked 
if(@d>=14)
BEGIN
update QuestionAsked
set IsComplete='true'
where Answer_id=1
END

Everytime I run this query only the records of first row gets updated. Rest all other rows are unaffected inspite of the fact that my tables first five rows contains the date whose difference with the current date is 14

Please help me out with this!!!

Thanks in Advance :)

[pritaeas]

Isn't the WHERE Answer_id = 1 causing this ?

[BitBlt]

With the construct you are using, you appear to be thinking more like procedural programming. That is, where you get a record, decide what to do, do it, then get another record, decide what to do, et cetera, etc. Even though Transact-SQL can be used that way (with "cursors") your code isn't set up properly to even do that.

Think "Set-at-a-Time", not "Row-at-a-Time". You could update an entire set of rows by specifying the selection criterion in the update statement itself. It might look something like this:

update QuestionAsked
set IsComplete='true'
where Datediff(dd,DateofAsking,getdate()) >= 14

That way you dispense with local variables, the conditional statement, or even identifying individual rows.

Hope this helps!

[adam_k]

What BitBIt is saying is absolutely correct. SQL server is build to manipulate multiple records simultaneously and that's when it will show it's true potential.

Back to your problem: The reason why you only get 1 record updated is the use of variable. The variable can hold 1 result and so that is what the server is processing.
Use BitBIt's query to do the same job in a fraction of the time and with minimum resources.

[arsheena.alam]

@pritaeas,@adam_k, @BitBit thanks alot for your help!! :)

@BitBit Your Query worked and I am able to get the desired result!!!! :)