Home » SQL & PL/SQL » SQL & PL/SQL » Different rows result between SELECT and UPDATE statments (oracle 9.2.0.5)
Different rows result between SELECT and UPDATE statments [message #358782] Wed, 12 November 2008 08:40 Go to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

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

WHen i run the update statment:

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.


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

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.



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



Thanks.
Sam../fa/5284/0/./fa/5284/0/
Re: Different rows result between SELECT and UPDATE statments [message #358787 is a reply to message #358782] Wed, 12 November 2008 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you select after or before the update?

Regards
Michel
Re: Different rows result between SELECT and UPDATE statments [message #358800 is a reply to message #358787] Wed, 12 November 2008 09:20 Go to previous messageGo to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

i tried:
1. Select after that update (rollback)
2. Update(rollback) after that select.

The same different.
Re: Different rows result between SELECT and UPDATE statments [message #358801 is a reply to message #358782] Wed, 12 November 2008 09:24 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do those 4 functions in the where clause do?
I'm betting their results vary depending on the values of the columns you're updating.
Re: Different rows result between SELECT and UPDATE statments [message #358804 is a reply to message #358801] Wed, 12 November 2008 09:34 Go to previous messageGo to next message
sammeras
Messages: 28
Registered: September 2007
Location: Israel
Junior Member

The functions return values after calculate.

But when i tried to run (update just 1 column):

UPDATE end_camp_accounts tt SET 
    tt.LAST_ACTION_CODE = 6
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
);


I got 3377 rows updated NOT 2462 rows updateed.
HMMMMMMMM...so strange, is it??
Re: Different rows result between SELECT and UPDATE statments [message #358810 is a reply to message #358804] Wed, 12 November 2008 09:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does the number of rows updated vary every time you change the number of columns, or has it settled down to a different, stable number?


Please post the code for one of those functions in the where clause.
Re: Different rows result between SELECT and UPDATE statments [message #358811 is a reply to message #358782] Wed, 12 November 2008 09:53 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think that proves what I suspected.
The results of your functions vary due to the update.
If you want more detail on where you're going wrong you'll need to supply the code of those functions.
Previous Topic: Strange Invalid Cursor error with sys_refcursor
Next Topic: Trigger problem
Goto Forum:
  


Current Time: Sat Dec 10 16:26:14 CST 2016

Total time taken to generate the page: 0.07645 seconds