| update using multiple tables [message #22847] |
Fri, 01 November 2002 09:03  |
Pam Church
Messages: 1
Registered: November 2002
|
Junior Member |
|
|
Am trying to update a table using information in a temp table. There are 3000 records in a table I want to update that table with data from a temp_table that has only 861 records in it,(there are only 861 in the first table that need updates. Our sql stmt looks something like this.
update table1
set item1 = (select temp_item1
from table2
where table2.key = table1.key and
table2.key2 = table1.key2 and
table2.key3 = table1.key3)
We're receiving the message 'Cannot update table1.item1 to Null'. We not trying to update to a null, we only want to update table with one piece of information from table 2. What are we doing wrong? Any suggestions would be appreciated.
|
|
|
|
| Re: update using multiple tables [message #22849 is a reply to message #22847] |
Fri, 01 November 2002 09:29  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
That's because you're trying to update each row in table 1 - you only want to update the ones where there's a matching record in table2.
Try something like this (I haven't tested it on tables, so it may need fixing by you).
update table1
set item1 = (select temp_item1
from table2
where table2.key = table1.key and
table2.key2 = table1.key2 and
table2.key3 = table1.key3)
where (table1.key , table1.key2, table1.key3)in
select table2.key , table2.key2, table2.key3
from table2;
Try EXISTS syntax too.
|
|
|
|
| Re: update using multiple tables [message #22873 is a reply to message #22847] |
Sun, 03 November 2002 13:25 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Try it out with adding a NOT NULL condition. Unless you are sure that the subquery returns only one row (ie. temp_item1) for each update of table1, try also adding a rownum check to take only the first one from the subquery. To make sure whether the subquery returns only one row and of no nulls, first try it out with a simple SELECT query with same conditions.
SELECT * FROM TABLE1 WHERE -- replaced line
item1 = (select temp_item1
from table2
where table2.key = table1.key and
table2.key2 = table1.key2 and
table2.key3 = table1.key3)
If this returns the rows as you desired, then try to investigate what kind values you have in those rows/columns of table1 and tabl2. In case, it does not return values as you desired, the try adding NOT NULL check in the subquery, just like
SELECT * FROM TABLE1 WHERE -- replaced line
item1 = (select temp_item1
from table2
where table2.key = table1.key and
table2.key2 = table1.key2 and
table2.key3 = table1.key3
AND TEMP_ITEM1 IS NOT NULL -- added line
)
Dont forget to remove my comments ('replaced line' and 'added line') when you run these tests.
If you still can not figure it out what the problem is, let me know with a sample of your data and the error ORACLE returned.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
