Home » SQL & PL/SQL » SQL & PL/SQL » doubts on update (merged)
doubts on update (merged) [message #328631] Sat, 21 June 2008 00:12 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi

I have these values

SQL> select count(*) from base_live_and_ceased_old;

  COUNT(*)
----------
   2642876

SQL> select count(*) from sac_key_mod;

  COUNT(*)
----------
     20455



SQL> desc sac_key_mod
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAC_SUB_KEY                                        VARCHAR2(6)


SQL> select count(a.sac_code) from  base_live_and_ceased_old a,sac_key_mod b 
  2  where trim(a.sac_code)=b.SAC_SUB_KEY;

COUNT(A.SAC_CODE)
-----------------
            68987



here when i run this query to update the sme_id i get all rows updated but i am only updating the matching ones?

How to differentiate?

SQL> update /*+ parallel(a,5,1) */ base_live_and_ceased_old a
  2  set a.sme_id=(select /*+ parallel(b,5,1) */ b.sac_sub_key from sac_key_mod b
  3  where b.SAC_SUB_KEY=trim(a.sac_code));

2642876 rows updated.



doubts on update [message #328632 is a reply to message #328631] Sat, 21 June 2008 00:19 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
SQL> select count(*) from base_live_and_ceased_old;

  COUNT(*)
----------
   2642876

SQL> select count(*) from sac_key_mod;

  COUNT(*)
----------
     20455
SQL> desc sac_key_mod
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAC_SUB_KEY                                        VARCHAR2(6)

SQL> select count(a.sac_code) from  base_live_and_ceased_old a,sac_key_mod b 
  2  where trim(a.sac_code)=b.SAC_SUB_KEY;

COUNT(A.SAC_CODE)
-----------------
            68987

SQL> update /*+ parallel(a,5,1) */ base_live_and_ceased_old a
  2  set a.sme_id=(select /*+ parallel(b,5,1) */ b.sac_sub_key from sac_key_mod b
  3  where b.SAC_SUB_KEY=trim(a.sac_code));

2642876 rows updated.


Here i am updating the smeid but i do get all the rows updated,the inner query only fetches the matching records!!!1


please advice how to correct this?

Thanks
Re: doubts on update [message #328635 is a reply to message #328632] Sat, 21 June 2008 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no where clause on your update statement so you update ALL rows.

Regads
Michel
Re: doubts on update [message #328637 is a reply to message #328632] Sat, 21 June 2008 00:57 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Thanks

This is what i am facing,when i run this query i am getting the respective matched rows updated but where the match is not found the sme id is updated as null,i want for non matching rows the sme id should not change?

Can you pls advice?

Thanks
Re: doubts on update [message #328638 is a reply to message #328637] Sat, 21 June 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add a where clause in your update statement.

Regards
Michel
Re: doubts on update [message #328643 is a reply to message #328638] Sat, 21 June 2008 01:57 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
SQL> update /*+ parallel(a,5,1) */ base_live_and_ceased_i_bkp
  2  a set a.sme_id=(select /*+ parallel(b,5,1) */ b.sac_sub_key from sac_key_mod b
  3  where trim(a.sac_code)=b.SAC_SUB_KEY)
  4  where exists (select 1 from  sac_key_mod b where trim(a.sac_code)=b.SAC_SUB_KEY);

68987 rows updated.


This looks like it will do?Can you confirm please?
Re: doubts on update [message #328644 is a reply to message #328643] Sat, 21 June 2008 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but DON'T use parallel.

Regards
Michel
Re: doubts on update [message #328645 is a reply to message #328644] Sat, 21 June 2008 02:11 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
well am altering the session and enabling parallel dml so why not??
Re: doubts on update [message #328652 is a reply to message #328645] Sat, 21 June 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you hire 5 developers if you want to write a 200 lines procedure? why not?

Regards
Michel
Re: doubts on update [message #328799 is a reply to message #328652] Sun, 22 June 2008 22:10 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Razz you are right Michel
Previous Topic: Need Help With Simple PL/SQL Script
Next Topic: please find me a soln for this
Goto Forum:
  


Current Time: Wed Dec 07 18:27:06 CST 2016

Total time taken to generate the page: 0.17069 seconds