Home » SQL & PL/SQL » SQL & PL/SQL » Shows null on update & Update not work for select statement (merged)
Shows null on update & Update not work for select statement (merged) [message #427809] Sun, 25 October 2009 22:07 Go to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi all, i got a code that will change a data in a column to data in another column...

Here are the codes

update            
(select 
a.id a_ssn,
replace((replace(b.ssn,'-','')),(substr((replace(b.ssn,'-','')), 4, 6)),b.emplid) a_change
from table_B b, table_A a, table_N N
where N.NATIONAL_ID_TYPE='PR'
and a.member_ssn = n.national_id
and b.emplid = n.emplid)
set a_ssn = a_change


i want to change id from table A to mixed up data from table B. But as i check the select statement, it shows the data as expected...a_ssn = before, a_change = after...but when i try to update, it shows this error...

ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table


Kindly guide me on this, thanks a lot
Re: Update not work for select statement [message #427810 is a reply to message #427809] Sun, 25 October 2009 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+ORA-01779:+cannot+modify+a+column+which+maps+to+a+non+key-preserved+table

01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
//         map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.


[Updated on: Sun, 25 October 2009 22:21]

Report message to a moderator

Shows null on update [message #427836 is a reply to message #427809] Mon, 26 October 2009 02:51 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi all, i hav a code that will update column ssn from table ps_A to mixed ssn from table ps_B.

My doubt here is when i execute it, an error shows like this :

ORA-01407: cannot update ("SHAH"."PS_A"."SSN") to NULL

I have check that there's no null value inside ps_B table and ps_A table. Then i try to execute it a bunch of rows by putting where rownum < 100 or 50...it works as expected

Here's are the code...kindly guide me on this...thanks


update ps_A a set
          a.ssn= (select replace((replace(b.ssn,'-','')),(substr((replace(b.ssn,'-','')), 4, 6)),b.emplid) from PS_B b
                  where b.NATIONAL_ID_TYPE='PR'
                  and (replace(b.ssn,'-','')) = a.ssn)

Re: Shows null on update [message #427842 is a reply to message #427836] Mon, 26 October 2009 03:04 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
As the UPDATE statement contains no WHERE clause, it updates all rows in PS_A. When there is no matching row in PS_B, it is updated to NULL.

If you want to update only those rows in PS_A with matching row in PS_B, add the appropriate WHERE clause:
WHERE EXISTS (SELECT 1
              FROM PS_B b
              WHERE b.NATIONAL_ID_TYPE='PR'
                AND (replace(b.ssn,'-','')) = a.ssn)

If you want to find PS_A rows without matching row in PS_B, simply SELECT them:
SELECT *
FROM PS_A a
WHERE NOT EXISTS (<the same subselect as above>)
Re: Shows null on update [message #427844 is a reply to message #427836] Mon, 26 October 2009 03:06 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL> update emp
2 set empno=(select EMPLOYEE_ID from hr.employees where EMPLOYEE_ID=10) where sal=800;
set empno=(select EMPLOYEE_ID from hr.employees where EMPLOYEE_ID=10) where sal=800
*
ERROR at line 2:
ORA-01407: cannot update ("SCOTT"."EMP"."EMPNO") to NULL


SQL> ed
Wrote file afiedt.buf

1 update emp
2* set empno=(select EMPLOYEE_ID from hr.employees where EMPLOYEE_ID=100) where sal=800
SQL> /

1 row updated.

SQL> select empno from emp
2 where sal=800;

EMPNO
----------
100

[Updated on: Mon, 26 October 2009 03:12]

Report message to a moderator

Re: Shows null on update [message #427851 is a reply to message #427844] Mon, 26 October 2009 03:39 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Thanks, the problem solve...just in a matter of time...it took a quite of it for a data around 26000...then try to change the code into this...

update
(select nvl(replace((replace(b.ssn,'-','')),(substr((replace(b.ssn,'-','')), 4, 6)),b.emplid),a.ssn) a_change, a.ssn a_ssn 
from PS_B b, ps_A a
where b.NATIONAL_ID_TYPE='PR'
and  b.ssn = (replace(a.ssn,'-','')))
set a_ssn = a_change




Then i got this error :

ORA-01779: cannot modify a column which maps to a non key-preserved table

Is there any ways to overcome this error


P/S :
The result for the subquery is like this
A_ssn A_change
001054091 001621297
001023293 001704875
001023293 001704875
002054947 002442204

Re: Shows null on update [message #427854 is a reply to message #427851] Mon, 26 October 2009 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/mv/msg/151462/427810/102589/#msg_427810
Now it is above:
http://www.orafaq.com/forum/mv/msg/151473/427810/102589/#msg_427810

Regards
Michel

[Updated on: Mon, 26 October 2009 03:58]

Report message to a moderator

Re: Shows null on update [message #427855 is a reply to message #427851] Mon, 26 October 2009 03:58 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
carillpower wrote on Mon, 26 October 2009 09:39
Is there any ways to overcome this error

Three possible ways:
- use the WHERE condition in UPDATE statement as I suggested
- create at least unique constraint on PS_B.SSN column. Unique content is not enough. For details, have a look into this AskTom post: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:548422757486#185496200346468396 (or search for ORA-01779 anywhere)
- use MERGE (see details in the thread posted above) - here unique content of PS_B.SSN mapped to PS_A suffices
Re: Shows null on update [message #427866 is a reply to message #427855] Mon, 26 October 2009 04:44 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Thanks for the suggestion, i decided to try on merge as i never use it before... as i create merge process, it keeps on show this error to me :S

SQL> MERGE INTO ps_A a
  2    USING
  3      (select ssn, emplid from ps_B) b
  4    ON (b.ssn = a.ssn)
  5    WHEN MATCHED THEN
  6      UPDATE SET a.ssn = replace((replace(b.ssn,'-','')),(substr((replace(b.s
sn,'-','')), 4, 6)),b.emplid);


    UPDATE SET a.ssn = replace((replace(b.ssn,'-','')),(substr((replace(b.ssn,'-
','')), 4, 6)),b.emplid)

                       *
ERROR at line 6:
ORA-00905: missing keyword


SQL>




I didn't have a clue on this, i have followed the step same like the attaches link...kindly shows me if there's sumthing that i miss out
Re: Shows null on update [message #427875 is a reply to message #427866] Mon, 26 October 2009 04:58 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
carillpower wrote on Mon, 26 October 2009 10:44

I didn't have a clue on this, i have followed the step same like the attaches link...kindly shows me if there's sumthing that i miss out

What reading the documentation for MERGE statement for your Oracle version (which I am unable to find anywhere)? It is described in SQL Reference book, available e.g. online on http://tahiti.oracle.com/.

In 9i and earlier, the WHEN NOT MATCHED clause is mandatory. You may search in the same thread for a "workaround" e.g. here.
Previous Topic: Self Join to fetch required data
Next Topic: Need help with a SQL Query
Goto Forum:
  


Current Time: Fri Dec 09 03:39:05 CST 2016

Total time taken to generate the page: 0.13718 seconds