Update skipping records when run in SQL Server Agent job???

Question

Update skipping records when run in SQL Server Agent job???

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Mia G (7/21/2009)
select acctNum as acno into #inactiveAccts
from acctMainTbl
where acctNum not in (select acctNum from deceasedAcctTbl)
and acctNum not in (select acctNum from oneYrActList)
update activeField
set activeField = (case when acctNum is not null then 'Y' else 'N' end)
from acctMainTbl left outer join #inactiveAccts on acctNum=acno

Hi,

Any specific reason you update the records in the temp table and again update the main table by the reference of the temp table.

You try like

update acctMainTbl

set activeField = (case when (acctNum is not null) or (acctNum '') then 'Y' else 'N' end)

where acctNum not in (select acctNum from deceasedAcctTbl)

and acctNum not in (select acctNum from oneYrActList)

And how you determine value of the deceasedAcctTbl and oneYrActList is may the criteria for the mismatch of the status.

Mia G Old Hand Points: 374 More actions · Answer 2

No, I'm not sure why the original developer chose to do it that way. Perhaps for simplicity (personal sanity) since the logic they used to determine the oneYrActList is quite involved.

However, even in the current structure when it comes to the update statement either an account is in the list or it's not. So, it should be marked with something once it is run.

I might try putting it together as suggested to see if it makes a difference. However, since it seems to work when I'm testing (just not in the scheduled production job) I don't know if I will be able to validate the change as needed. I'll think on that some more.

Thanks.

sgmunson SSC Guru Points: 110651 More actions · Answer 3

When you eliminate the impossible, whatever remains, however improbable, must be true. I suspect that your left outer join is becoming an INNER JOIN by virtue of a where clause that specifies a field in the table you left joined to. Although you didn't provide the actual query, you did state that there was other logic that was significant, and it seems likely that something in the UPDATE part has a where clause item looking at a field in the joined table, which SQL Server will turn into an INNER JOIN. One quick way to identify this is to query for all records that have blank values before this job runs, and then do the same after the job runs, and see if there's any difference. It's possible you may find your answer that way.

Steve

(aka smunson)

:-):-):-)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mia G Old Hand Points: 374 More actions · Answer 4

I actually just went ahead and rewrote the query anyway for some performance improvements. Then I was planning to take some steps similar to what you suggested the next time it runs. So, between doing that and the changes I've made, I'm hopeful the situation will be resolved.

Thanks for the input!