Re: Best way to update large field

In that case you are incorrectly embedding dynamic sql in a static query. The syntax you have now is simply *incorrect*. Period. The update statement thinks you are assigning it a column named "<etc>" This is wrong.
From my end it looks like you *need* a subquery. It also looks like you don't want to believe me. If that's true, I can't help you any further.

Regards,

Sybrand Bakker, Oracle DBA

"James Williams" <techsup_at_mindspring.com> wrote in message news:3b0c1b67.119357188_at_news.mindspring.com...
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
>
> Actually another program reads this column and uses it to create DML.
> The data to be updated is a literal. It is not a subquery.
>
> >The syntax is
> >update <table>
> >set <column>= ( <subquery>)
> >
> >NOT
> >set <column > = "<subquery>"
> >
> >Regards,
> >
> >Sybrand Bakker, Oracle DBA
> >
> >"James Williams" <techsup_at_mindspring.com> wrote in message
> >news:3b0bf935.110601234_at_news.mindspring.com...
> >> Get the dread ora_00972 trying to do the below. I can insert the row
> >> but would like to update an existing row. Any good id
> >>
> >> SQL> @order
> >> ERROR:
> >> ORA-00972: identifier is too long
> >>
> >>
> >> SQL> edit order
> >> No lines in the buffer
> >> "order.sql" 3 lines, 331 characters
> >> 1 UPDATE control
> >> 2 SET ENTITY_LIST_SQL = "select (entity_key) from it_order where
> >> to_date(
> >> due_dte, 'YYYYMMDD' ) < (sysdate - entity_criteria) and cmpl_stat_cd
> >> NOT LIKE '
> >> %' and cmpl_stat_cd IS NOT NULL and it_order.cis_ordr_nbr in (select
> >> cis_ordr_n
> >> br from upl_detail_at_purge38) order by (entity_key)"
> >> 3 WHERE ENTITY = 'ORDER'
> >> ~
> >>
> >> SQL> show user
> >> USER is "XXX"
> >> SQL> desc control;
> >> Name Null? Type
> >> ----------------------------------------- --------
> >> ----------------------------
> >> ENTITY NOT NULL VARCHAR2(30)
> >> PROCESS_TYPE NOT NULL VARCHAR2(1)
> >> ENTITY_CRITERIA NOT NULL VARCHAR2(30)
> >> COMMIT_COUNTER NOT NULL NUMBER(8)
> >> LUD_START DATE
> >> LUD_STOP DATE
> >> REVISION_CD NOT NULL VARCHAR2(1)
> >> LUD NOT NULL DATE
> >> HISTORY_FLAG VARCHAR2(1)
> >> ENTITY_LIST_SQL VARCHAR2(500)
> >> DESCRIPTION VARCHAR2(5
> >
> >
>
Received on Wed May 23 2001 - 16:07:02 CDT