Home » SQL & PL/SQL » SQL & PL/SQL » Updating....
Updating.... [message #221572] Tue, 27 February 2007 09:06 Go to next message
karrisql.com
Messages: 7
Registered: November 2006
Junior Member
I need to update several rows with select-
clause. Select returns right data, but update statements
gives: ORA-01407: cannot update to NULL
tab2 has only about 100 rows and tab1 has several
thousand.
Can U help me?

update tab1 t1
set col1=(
SELECT col2_1
FROM tab2 t2
where col2_1 is not null
and t1.col1_3=t2.col2_3)

Re: Updating.... [message #221583 is a reply to message #221572] Tue, 27 February 2007 09:37 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
What you are missing is that the update statement has no where clause to restrict which rows are updated. It will therefore update every row in the table: if there is no match from the subquery, it will update the column to null. To correct it, you need to add a where clause:

update tab1 t1
set col1=(
SELECT col2_1
FROM tab2 t2
where col2_1 is not null
and t1.col1_3=t2.col2_3)
where exists(
SELECT 1
FROM tab2 t2
where col2_1 is not null
and t1.col1_3=t2.col2_3)

[Updated on: Tue, 27 February 2007 09:38]

Report message to a moderator

Previous Topic: Problem In Loading The CSV File Into The Database
Next Topic: PLS-00201 - identifier declaration error - in resultset query
Goto Forum:
  


Current Time: Sun Dec 11 08:28:38 CST 2016

Total time taken to generate the page: 0.09970 seconds