Member Avatar for 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 :)

  • 4 Contributors
  • 4 Replies
  • 237 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by arsheena.alam

Dani AI

Generated

A few clarifications and a safer, more scalable approach that build on what , and already pointed out.

The original script combined a scalar variable and an explicit filter (Answer_id = 1), so only a single value was ever captured and only one row was targeted. A set-based UPDATE is the cleaner solution for this kind of change. Before updating, verify the exact rows that will be affected and adapt the IsComplete literal to the column type (bit vs varchar).

Example verification and a SARGable update (avoids applying a function to the DateofAsking column so an index can be used):

SELECT Answer_id, DateofAsking, IsComplete
FROM QuestionAsked
WHERE DateofAsking <= DATEADD(day, -14, CAST(GETDATE() AS date))
  AND (IsComplete IS NULL OR IsComplete <> 'true');

UPDATE QA
SET IsComplete = 'true'
FROM QuestionAsked AS QA
WHERE QA.DateofAsking <= DATEADD(day, -14, CAST(GETDATE() AS date))
  AND (QA.IsComplete IS NULL OR QA.IsComplete <> 'true');

Notes and cautions:

  • Using DATEADD/<= is generally SARGable; wrapping the column in DATEDIFF (or other functions) prevents use of an index on DateofAsking and hurts performance.
  • CAST(... AS date) removes time-of-day; on SQL Server versions without the date type, use DATEADD(day, -14, GETDATE()) but be aware of time components and off-by-one effects.
  • For large tables, update in controlled batches (TOP N loop) to reduce locks and log growth; check @@ROWCOUNT and repeat until zero.
  • Always run the SELECT first, confirm row count, and ensure the literal used for IsComplete matches its data type. Transactional backups or an explicit transaction can protect against accidental mass updates.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas

Isn't the WHERE Answer_id = 1 causing this ?

Member Avatar for 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!

commented: Agree +7
Member Avatar for 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.

Member Avatar for arsheena.alam

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.