In the update, you can't specify a list of columns to update and then a list
of values to match 1:1. You have to provide an assignment for each column
separately. The other problem is that you're probably getting a cartesian
product in the join. Can I assume the two lines:
> s2.col_number(+) = i1.col3_number
> s4.col_number(+) = i1.col3_number
s.b. s3.col_number(+) in the first, and i1.col4_number in the 2nd?
You have to specify the tables relationships fully in the join, or you'll
get garbage back.
--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
buzkoff_at_my-dejanews.com wrote in message <74pbrm$cs$1_at_nnrp1.dejanews.com>...
>
>
>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
