Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

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
0
sammeras
Asked:
sammeras
2 Solutions
 
awking00Commented:
What does the attached query show?
query.txt
0
 
sammerasAuthor Commented:
The query shows 81 results.
0
 
sujith80Commented:
Do you have any triggers on the table?

If so, that could explain it.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
sujith80Commented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now