Re: Best way to update large field

It seems James wants to assign a literal to a column, but he uses wrong syntax: he encloses the literal in double quotes instead of single quotes. But Sybrand wants to force James use a subquery.

So here is a compromise:

UPDATE control
SET ENTITY_LIST_SQL = (select 'your literal here' from dual);

Now James' update statement works and includes a subquery!

--
Giorgos

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:tgo9flk75q8l6c_at_beta-news.demon.nl...


>

> 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

> > >

> > >

> >

>

>