Different rows result between SELECT and UPDATE statments.

Hello,
I have strange behavior with select and update statment.

WHen i run the update statment:

::SEE UPDATE SCRIPT::

If i run the select statment...with the same condetion and values i got 81 rows:

::SEE SELECT SCRIPT::


Why i got different results ?
WHere is the problem? and how can resolve it?



Thanks.
Sam.
--UPDATE SCRIPT 
UPDATE end_camp_accounts tt SET 
    tt.LAST_ACTION_CODE = 6,
    tt.last_action_date=sysdate+1,
    tt.last_action_user='J83'
WHERE  tt.last_action_code NOT IN (6,0,4)
AND tt.end_date <> trunc(sysdate+1)
AND (
    end_camp_acc_is_active(tt.record_id) = 0
    OR
    end_camp_acc_is_hasava(tt.record_id) = 1
    OR
    end_camp_want_disconnect(tt.record_id)=1
    OR
    end_camp_want_hasava(tt.record_id)=1
);
 
2462 rows updateed.
 
-- ####################################
 
-- SELECT SCRIPT
SELECT 
    record_id 
FROM end_camp_accounts tt
WHERE  tt.last_action_code NOT IN (6,0,4)
AND tt.end_date <> trunc(sysdate+1)
AND (
    end_camp_acc_is_active(tt.record_id) = 0
    OR
    end_camp_acc_is_hasava(tt.record_id) = 1
    OR    
    end_camp_want_disconnect(tt.record_id)=1
    OR
    end_camp_want_hasava(tt.record_id)=1
);
 
81 rows selected.

Open in new window

select.GIF
update.GIF
sammerasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

awking00Commented:
What does the attached query show?
query.txt
0
sammerasAuthor Commented:
The query shows 81 results.
0
SujithData ArchitectCommented:
Do you have any triggers on the table?

If so, that could explain it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shaju KumbalathDeputy General Manager - ITCommented:
one chance is that u may have select policies for specific users on that table
0
yuchingCommented:
I think you execute update first, in the update code, you already update LAST_ACTION_CODE = 6;

Hence when you select later (the selection last_action_code NOT IN (6,0,4) ), the record updated later didnt show out because the last_action_code NOT in 6, that why it explains only 81 rows return for select.

Instead , you should select 1st then only Update, then the count will match
0
SujithData ArchitectCommented:
the proposed answer will not fix the counts.

It is clearly because the table has triggers defined on it. That count mismatch will appear in whatever way the updates are done.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.