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  |
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
|
|
|
|
Shows null on update [message #427836 is a reply to message #427809] |
Mon, 26 October 2009 02:51   |
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   |
flyboy
Messages: 1903 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   |
 |
ramoradba
Messages: 2457 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   |
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 #427866 is a reply to message #427855] |
Mon, 26 October 2009 04:44   |
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  |
flyboy
Messages: 1903 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.
|
|
|
Goto Forum:
Current Time: Thu Feb 13 22:28:47 CST 2025
|