How to update a column only once in a SQL Server Agent job that runs every 5 minutes

Question

I want to run this code only once in an agent job that gets hit every 5 minutes:

UPDATE mytable
SET text_column = (CONCAT('Added text - ', text_column))
WHERE status IN ('status1', 'status2', 'status3')
  AND other_criteria IN ('other_criteria1', 'other_criteria2', 'other_criteria3');

New records may get inserted any time of day and night - which is why the job runs every 5 min.

text_column may or may not be NULL when the record is inserted (which doesn't matter but, prevents me from using ISNULL in my where clause.)

status may change on the fly before the job runs (which also doesn't matter except that I cannot update the above query to only run against those specified statuses and then change the status after to ensure that the update doesn't happen again [because other transactions may still need to happen while the record is in whatever status].)

other_criteria is generally static information but, it is by no means unique...

So, what I want to happen is basically:

5 minute job runs - the new record = status1 & other_criteria1 gets the text_column set to 'Added_text - (text_column)'

Then I want to avoid having this record's text column set to 'Added text - Added text - (text_column)' the next time the job runs even when it still matches the "status" and "other_criteria" in my where clause...

Is this possible without getting more specific in my where clause?

1

use trigger instead – Alex Kudryashev Jun 06 '19 at 21:24

M. Kanarkowski · Answer 1 · 2019-06-06T21:25:28.903

I would introduce one more column to your table

alter yourtable add column WasUpdated smallint default 0

Then add one more simple clause to your statement. A new record will have this set to zero and after first update set to 1, so one row will be updated only once.

UPDATE mytable
SET text_column=(CONCAT('Added text - ', text_column))
    ,wasUpdated = 1
WHERE status
IN ('status1', 'status2', 'status3')
AND other_criteria
IN ('other_criteria1', 'other_criteria2', 'other_criteria3')
AND wasUpdated = 0;

Such column won't introduce any headache, unless you have select * from in your procedures etc.

As Vitaly Borisov sugested you can also create a date column. With this approach you will know exactly when a row was updated.

  alter yourtable add column Updateddate datetime2 

    UPDATE mytable
    SET text_column=(CONCAT('Added text - ', text_column))
        ,Updateddate = getdate()
    WHERE status
    IN ('status1', 'status2', 'status3')
    AND other_criteria
    IN ('other_criteria1', 'other_criteria2', 'other_criteria3')
    AND Updateddate  is null;
Such column won't introduce any headache, unless you have `select * from` in your procedures etc.

Agree, but I'd go with Datetime2 WhenUpdated flag - so logic can be based on time, not just a fact it was updated. — Vitaly Borisov, Jun 06 '19 at 21:15
Sure, it can be done your way too. Surely, your solution has a benefit regarding checking the data i.e. when rows were updated etc. — M. Kanarkowski, Jun 06 '19 at 21:20

score 2 · Accepted Answer · answered Jun 06 '19 at 22:10

The simplest way is to filter out added text:

UPDATE mytable
SET text_column=(CONCAT('Added text - ', text_column))
WHERE status IN ('status1', 'status2', 'status3')
AND other_criteria IN ('other_criteria1', 'other_criteria2', 'other_criteria3')
AND text_column NOT LIKE 'Added text - %';

How to update a column only once in a SQL Server Agent job that runs every 5 minutes

2 Answers2