Home » SQL & PL/SQL » SQL & PL/SQL » Update statement generates error - SQL command not ended properly
Update statement generates error - SQL command not ended properly [message #248318] Thu, 28 June 2007 11:13 Go to next message
concorde800
Messages: 52
Registered: May 2007
Member
I am trying the below UPDATE statement and it fails with error
SQL Command not ended properly !!!

THe problem is in the string for the where clause, but don't know how to correct this one?

How do I get this update to work?

Thank you.


UPDATE 
    DM_COMMON.ZZZ_LOAD_META
SET
    WHERE_CLAUSE = 'SO.KEY_SLS_ORDR_FACT_ID IN 
       (SELECT /*+ PARALLEL (INC, 2) */ INC_SLS_ORDR_FACT_ID
          FROM DM_COMMON.SO_FACT_INC_KEYS_BASIC INC
        WHERE INC.KEY_SLS_ORDR_FACT_ID = SO.KEY_SLS_ORDR_FACT_ID
           AND INC.RCRD_DELETE_FLAG = 'Y')'
WHERE CONFIG_NAME IN ('DEL_SO_EMEA', 'DEL_SO_USA', 'DEL_SO_ASIA', 'DEL_SO_AMERICAS')

Re: Update statement generates error - SQL command not ended properly [message #248323 is a reply to message #248318] Thu, 28 June 2007 11:28 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
the single quote marks in the WHERE_CLAUSE assignment are wrong/confusing the compiler
Re: Update statement generates error - SQL command not ended properly [message #248325 is a reply to message #248323] Thu, 28 June 2007 11:37 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Thank you.

I tried to put "" around Y, it appears to be syntactically correct.
Is there any alternative other than this method?

This is what I tried.

UPDATE 

    DM_COMMON.ZZZ_LOAD_META

SET

    WHERE_CLAUSE = 'SO.KEY_SLS_ORDR_FACT_ID IN (SELECT /*+ PARALLEL (INC, 2) */

                                                    INC.KEY_SLS_ORDR_FACT_ID

                                                    FROM DM_COMMON.SO_FACT_INC_KEYS_BASIC INC

                                                    WHERE INC.KEY_SLS_ORDR_FACT_ID = SO.KEY_SLS_ORDR_FACT_ID

                                                    AND INC.RCRD_DELETE_FLAG = "Y")'

WHERE CONFIG_NAME IN ('DEL_SO_EMEA', 'DEL_SO_USA', 'DEL_SO_ASIA', 'DEL_SO_AMERICAS')


Re: Update statement generates error - SQL command not ended properly [message #248327 is a reply to message #248325] Thu, 28 June 2007 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put " put ''

Regards
Michel
Re: Update statement generates error - SQL command not ended properly [message #248331 is a reply to message #248318] Thu, 28 June 2007 12:20 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
What Michel is saying is that when you have to include a single quote within a quoted string, ou simply use 2 single quotes next to each other to pass a single quote to the table. For example

'this is ' a test'

is illegal, but

'this is '' a test'

would work.
Re: Update statement generates error - SQL command not ended properly [message #248333 is a reply to message #248331] Thu, 28 June 2007 12:25 Go to previous message
concorde800
Messages: 52
Registered: May 2007
Member
Thank you Bill and Michel.

The update statement worked.
Previous Topic: Function is not working in distributed transaction (with dblink)
Next Topic: running sql loader from pl/sql
Goto Forum:
  


Current Time: Wed Dec 07 18:37:09 CST 2016

Total time taken to generate the page: 0.07888 seconds