The problem is in the actual data you are working with. It is not what you
think it is.
For example,
in PORTLET you have
1,null
2,null
in PARAM_EXCEPTION
1, 'portlet_title', 'Text To Be Transfered'
1, 'portlet_title', 'Some other text'
1, 'different_title','Some other text'
2, 'Description2', 'Text Not To Be Transfered'
When your sql statement is processing (1,null) it finds that there are 2
PARAM_EXCEPTION
records that meet the criteria (portlet_code = 1 and param_name =
'portlet_title').
Both
1, 'portlet_title', 'Text To Be Transfered' and
1, 'portlet_title', 'Some other text'
meet the criteria.
Obviously it can not set A.description to be both 'Text To Be Transfered'
and 'Some other text'.
Remove the duplicates from your PARAM_EXCEPTION table and try
UPDATE portlet A
SET A.description = ( SELECT B.param_char_val
FROM param_exception B
WHERE A.portlet_code = B.portlet_code
and B.param_name = 'portlet_title'
and B.param_char_val is not null);
On Fri, Dec 12, 2008 at 1:03 PM, Vlad <[email protected]> wrote:
>
> I've just found a version of what I need which works for MS SQL Server
> 2005 - it doesn't work for oracle though:-
>
> UPDATE portlet
> SET portlet.description = param_exception.param_char_val
> FROM portlet, param_exception
> WHERE portlet.portlet_code = param_exception.portlet_code
> and param_exception.param_char_val is not null
> and param_name = 'portlet_title'
>
> The sample statements below should work for you (They took quite a bit
> of creating - is there an easy way to generate these statements)?
>
> I want select * from portlet to go from
>
> 1,NULL
>
> to
>
> 1,'Text To Be Transfered'
>
> Hope I've been clear - many thanks ;-)
>
> CREATE TABLE PORTLET
> (
> PORTLET_CODE VARCHAR2(20 BYTE) NOT NULL,
> DESCRIPTION VARCHAR2(50 BYTE),
> )
>
> INSERT INTO PORTLET (
> PORTLET_CODE, DESCRIPTION )
> VALUES ( 1,NULL );
>
> ALTER TABLE PARAM_EXCEPTION
> DROP PRIMARY KEY CASCADE;
> DROP TABLE RMGTEST.PARAM_EXCEPTION CASCADE CONSTRAINTS;
>
> CREATE TABLE PARAM_EXCEPTION
> (
> PORTLET_CODE VARCHAR2(20 BYTE) NOT NULL,
> PARAM_NAME VARCHAR2(20 BYTE) NOT NULL,
> PARAM_CHAR_VAL VARCHAR2(4000 BYTE),
> ) ;
>
>
> INSERT INTO PARAM_EXCEPTION (
> PORTAL_TYPE, STRUCTURE_CODE, OWNER_TYPE,
> OWNER, PORTLET_CODE, PARAM_NAME,
> PARAM_NUM_VAL, PARAM_CHAR_VAL, PARAM_DATE_VAL,
> PARAM_BOOL_VAL)
> VALUES ( 1,'Description','Text To Be Transfered' );
>
> INSERT INTO PARAM_EXCEPTION (
> PORTAL_TYPE, STRUCTURE_CODE, OWNER_TYPE,
> OWNER, PORTLET_CODE, PARAM_NAME,
> PARAM_NUM_VAL, PARAM_CHAR_VAL, PARAM_DATE_VAL,
> PARAM_BOOL_VAL)
> VALUES ( 2,'Description2','Text Not To Be Transfered' );
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
