Automatically Update Query SQL

Question

I'm trying to have my query auto-update every year, so that a certain "Debt" fee would increase every year, however it's not working properly, no matter what method I try. My goal is to make it auto-increase by 75000 every year. Here are a few things I tried that did not work.

CASE
        WHEN DATENAME(dy, GETDATE()) = 1 THEN Debt + 75000
        ELSE Debt
    END AS [Debtt]

I tried to use a "Debt = Debt + 75000" just like I know in Java, but I'd get an incorrect syntax at the "=" sign. The desired value would only work on the dayOfYear = 1, but would return back to initial value on dayOfYear = 2

I also tried this

UPDATE Table
SET Debt = Debt + 75000
WHERE DATENAME(dy, GETDATE()) = 1

This one would ONLY work when I'd execute the Query on THAT day. And if I keep executing it, the value would just keep increasing by 75000

And last but not least, I tried adding an IF statement, with a variable.

@MoneyDebt AS MONEY = 0

IF DATENAME(dy, GETDATE()) = 1 
BEGIN
    UPDATE initialTable
    SET @MoneyDebt = @MoneyDebt + 75000
END
-- useless statements
SELECT @MoneyDebt AS [Debt]

In this way, it actually makes MoneyDebt=0 when dayOfYear is different than 1, and when it's 1, for some reason it actually makes MoneyDebt = 150000 (which is twice the value desired)

I have been wrecking my brain over this for the past 3 days, trying every possibility and checking everything online. Is it simply not possible to make an automatic update on SQL?

It should be noted that I'm using SQL Server 2014

Gordon Linoff · Accepted Answer · 2015-08-30 03:32:08Z

3

What you want to do is a bit awkward. You can put in a job to run on the first of every year to automatically do the update.

However, my recommendation is simpler. Just keep the first year of the data and then do the increment by adding a value based on the number of years that have passed. You can do this in a view. Or, you can do this using a computed column:

alter table t
    add debtt as ( debt + 75000 + datediff(year, orig_date, getdate()) );

The column debtt will have the updated value.

edited Aug 30, 2015 at 3:32

answered Aug 30, 2015 at 1:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick Younes Over a year ago

That actually really helps as a concept, however it did not work, I STILL get an error to the "=" sign, saying there's an incorrect syntax

Gordon Linoff Over a year ago

@PatrickYounes . . . That's because the correct syntax uses as not =.

Drew · Accepted Answer · 2015-08-30 02:48:56Z

0

Edit at bottom coming up. Jump to the MSFT section below.

(this top chunk was just because the question somehow had the mysql tag in it initially, my apologies to you MSFT guys.)

MYSQL

Consider using mysql's Create Event strategy that can take away from needing to do cron jobs.

DELIMITER $$
CREATE EVENT annualThingEvent
ON SCHEDULE EVERY '1' YEAR
STARTS '2016-01-01 00:00:00'
DO 
BEGIN
 -- perform some annual thing, as complicated as you want
END$$
DELIMITER ;

From the Manual page on CREATE EVENT,

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

Properly setting up events to even happen and monitoring them is important.

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

ooops, the event scheduler is not turned on.

Well I can wait all day long, events aren't even turned on

SET GLOBAL event_scheduler = ON;  -- turn her on

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

List all events by schema name:

show events from so_gibberish;

or

show events\G; -- <--------- I like this one from mysql> prompt
show events; -- <--------- from workbench / sqlyog


*************************** 1. row ***************************
                  Db: so_gibberish
                Name: set_trips_finished
             Definer: GuySmiley@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2015-08-23 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

MSFT

Not that I know anything about it, but I include this because in going to delete the whole Answer, the OP asked. So keep that in mind. Lifted from here.

Overview

Scheduling is a very useful feature of every database management software. In the MS SQL Server the SQL Server Agent acts as the scheduler and it executes the tasks.

Explanation

SQL Server Agent is the equivalent of the MySQL Event Scheduler. It is part of every edition except MS SQL Express. It can be accessed from SQL Server Management Studio.

However, the MS SQL implementation is very different from the CREATE EVENT statement syntax in MySQL. You have to create jobs, job steps and schedules separately. There are two ways to perform this task, you can either use the GUI or create Transact-SQL statements.

The general steps to create a scheduled job using T-SQL are the following:

Execute sp_add_job to create a job.
Execute sp_add_jobstep to create each job step.
Execute sp_add_schedule to create a schedule. You can define more schedules, e.g. run at 3am and 11pm.
Execute sp_attach_schedule to link the schedule to the job.
Execute sp_add_jobserver to set the server for the job.

Also, look at this MSDN article about Implementing Jobs.

edited Aug 30, 2015 at 2:48

answered Aug 30, 2015 at 1:54

Drew

25k10 gold badges47 silver badges81 bronze badges

4 Comments

Drew Over a year ago

by the way, I just cut and paste that, I am not that fast

alroc Over a year ago

The poster is using SQL Server, not MySQL.

Patrick Younes Over a year ago

Is that a feature only available on MySQL? Sweet Jesus that sort of feature would help

Drew Over a year ago

Even if it can't schedule something say at the year level, a daily/hourly thing could fire off, looking at datetime datatype values and figuring if it is necessary. For instance, rows could exist helter skelter, 5 today, none for 11 days, then 3 more for that day, one on Jan2, 2016, etc. And it is picked up. Half the time you are just trying to get the thing kicked off automatically to look, then let the programmer's code figure out what to do. So it seems you have every tool in your tool chest with MSFT

Collectives™ on Stack Overflow

Automatically Update Query SQL

2 Answers 2

2 Comments

MYSQL

MSFT

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

MYSQL

MSFT

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related