How to change the table value automatically using ms sql server ?

[jacksantho]

Hi,

Anyone please help me out. Am having a table with field name 'status' with the values 'yes' or 'no'.

Really, i wants to change this 'yes' or 'no' status automatically using mssql server. But i don't have an idea to continue on this work.

for example, now the value is 'no' in the status field. I want to change 'no' value to 'yes' automatically till the from date(15-11-2011) to date(25-11-2011) and also should start at mid morning 12 AM, at the time of mentioning from date(15-11-2011).

By on 25-11-2011 mid night 11.59 PM or 12 AM, 'yes' value again should changes to 'no' value.

[adam_k]

I am not sure what you mean by automatically, so I'm leaving this part out for now.

To change the values between the 2 dates from no to yes you need an update query.
It should look like:

update table 
set status ='yes'
where status = 'no' 
and date between '15-11-2011 12:00:000' and '25-11-2011 12:00:000' -- you might want to copy the exact pm or am from a getdate() to make sure you've got it right

you can change the above to match your next update.

Back on the automatic thing: Do you mean to update everything at once like above or do you mean that it should a) check every value inserted and change it accordingly b) run every x secs/mins/days/months/etc and update as per your criteria?
If you didn't mean update everything at one go, then you need to be more detailed as to the dates (is it a fixed setting, is it calculated like today + x days?), the method that you wish it to run (do you want a trigger or a scheduled task?) and it might come in handy if you can tell the number of records affected each time/the traffic the table has (trigger will slow down the table, but a scheduled task that runs too often might even deadlock itself).

[jacksantho]

hi,
thanks for your info...

how can i add this update statement in the stored procedure. And how can i add this as a new job in the scheduled task. yours is confusing very much.

while on scheduling, from date (to) to date, update statement set to 'yes'( for two weeks), after this two weeks, status should change 'no'. please got it

[adam_k]

create procedure dbo.update_table
as 
begin 
update table 
set status ='yes'
where status ='no'
and date between '15-11-2011 12:00:000' and '25-11-2011 12:00:000' 
end

[jacksantho]

hi,

can i able to add this job for scheduling? how ?