| Updating.... [message #221572] |
Tue, 27 February 2007 09:06  |
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  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
