Update

[Vijay Patel]

Just like  following update query:

Update emp

set salary * 1.7

from emp where deptno = 10

I am performing folowing update and errored out on Error at Command Line:11 Column:4

Error report:

SQL Error: ORA-00933: SQL command not properly ended

00933. 00000 -  "SQL command not properly ended"

*Cause:    

*Action:

error comming from red highlighted area

update query is as follow:

update SU_S2H_MEMBER_DETAIL 

  set MD.SU_WAIVER_IND = 'Y',

MD.SU_WAIVER_SOURCE = null,

MD.SU_WAIVER_REASON = NULL,

MD.SU_WAIVER_NOTE = 'Tracking change',

   MD.SU_WAIVER_YMDWAIVE = TO_CHAR(sysdate,'YYYYMMDD'),

   MD.OP_NBR = 'MANL',

   MD.YMDTRANS = TO_CHAR(sysdate,'YYYYMMDD'),

   MD.SU_PROCESS_ID = 'PROG REQ',

   MD.SU_PROCESS_TIME = TO_CHAR(sysdate,'HHMISS')

   from AMIOWN.SU_S2H_MEMBER_DETAIL MD

  where MD.SU_INCENTIVE_TYPE = 'WELL';

    

      

  COMMIT;

  

[Michael Moore]

You have two types of DML mixed together in one statement. The FROM part would imply that you want values from that table, yet, you are setting all the values to hard-coded amounts. 

Remove the " from AMIOWN.SU_S2H_MEMBER_DETAIL MD" entirely. Then remove the MD alias from everywhere. This will give you a statements that works,  but I'm not sure if it will accomplish what you desire.

If you are trying to set the values of TABLEA based on the values of TABLEB then you need a correlated update which takes this form:

UPDATE <table_name> <alias>
SET (<column_name>,<column_name> ) = (
   SELECT (<column_name>, <column_name>)
   FROM <table_name>
   WHERE <alias.column_name> = <alias.column_name>)
WHERE <column_name> <condition> <value>;

https://www.google.com/search?q=sql+update+select&rlz=1C1CHFX_en__445__445&oq=sql+update+select&aqs=chrome..69i57j0l5.7079j0j7&sourceid=chrome&espv=210&es_sm=122&ie=UTF-8#es_sm=122&espv=210&newwindow=1&q=oracle+correlated+update

Mike

--
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle...@googlegroups.com
To unsubscribe from this group, send email to
Oracle-PLSQL...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
 
---
You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[ddf]

Comments embedded.

On Tuesday, November 19, 2013 1:39:25 PM UTC-7, Vijay Patel wrote:

Just like  following update query:

Update emp

set salary * 1.7

from emp where deptno = 10

Which is incorrect; you've already stated you're updating EMP in the UPDATE line, the FROM  line has no place in such an update.  The correct statement would be:

 

Update emp

set salary * 1.7

where deptno = 10;

 

To follow the correct example:

 

 

update SU_S2H_MEMBER_DETAIL

set MD.SU_WAIVER_IND = 'Y',

MD.SU_WAIVER_SOURCE = null,

MD.SU_WAIVER_REASON = NULL,

MD.SU_WAIVER_NOTE = 'Tracking change',

MD.SU_WAIVER_YMDWAIVE = TO_CHAR(sysdate,'YYYYMMDD'),

MD.OP_NBR = 'MANL',

MD.YMDTRANS = TO_CHAR(sysdate,'YYYYMMDD'),

MD.SU_PROCESS_ID = 'PROG REQ',

MD.SU_PROCESS_TIME = TO_CHAR(sysdate,'HHMISS')

where MD.SU_INCENTIVE_TYPE = 'WELL';

 

This performs the update you want to execute.

 

 

David Fitzjarrell

[Michael Moore]

I think you would have to remove the MD alias.

--

[ddf]

In my haste to respond I missed that.

 

Thanks.

 

 

 

David Fitzjarrell