Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> What the hell is going on with these correlation tables?
I'm trying to update column with a SELECT which is a joint using the following request:
UPDATE supertable i1
SET (col1_name, col2_name, col3_description, col4_description) = (SELECT Distinct NVL(c1.col_description,' '), NVL(c2.col_description,' '), NVL(c3.col_description,' '), NVL(c4.col_description,' ') FROM business s1, codes s2, accounts s3, offices s4 WHERE s1.col_number(+) = i1.col1_number and s2.col_number(+) = i1.col2_number and s2.col_number(+) = i1.col3_number and s4.col_number(+) = i1.col3_number
Outer join is used because columns in supertable may and maynot be translated with the satellites (business,codes,accounts,offices). The problem is that updated are only the records for which all 4 values may be found in translation tables. But when I check the SELECT with
SELECT
Distinct NVL(c1.col_description,' '), NVL(c2.col_description,' '), NVL(c3.col_description,' '), NVL(c4.col_description,' '), i1.* FROM business s1, codes s2, accounts s3, offices s4, supertable i1 WHERE s1.col_number(+) = i1.col1_number and s2.col_number(+) = i1.col2_number and s2.col_number(+) = i1.col3_number and s4.col_number(+) = i1.col3_number
I see all records retrieved. DOes anybody know what the trick is?
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Dec 10 1998 - 00:00:00 CST