CODEWITHSUNDEEP
sqloracle-databasesql-update
Marcus
Marcus

Reputation: 3869

ORA-01747: invalid user.table.column, table.column, or column specification in Oracle

have the following Update query which gives an error ORA-01747: invalid user.table.column, table.column, or column specification which i am not able to identify. I have the correct syntax i guess but still it gives the error. Here is the query:

Update TEMP_WF_WORKFLOW Set Event_ID=(Select 10003 from TEMP_WF_WORKFLOW TWW JOIN TMP_SOAP_MONITORING_IDS TSM ON TSM.SUBSCRIPTION_ID=TWW.SUBSCRIPTION_ID where TSM.order_type='SELF_REGISTRATION'),
Set Event_ID=(Select 10028 from TEMP_WF_WORKFLOW TWW JOIN TMP_SOAP_MONITORING_IDS TSM ON TSM.SUBSCRIPTION_ID=TWW.SUBSCRIPTION_ID where TSM.order_type='NEW'),    
Set Event_ID=(Select 10078 from TEMP_WF_WORKFLOW TWW JOIN TMP_SOAP_MONITORING_IDS TSM ON TSM.SUBSCRIPTION_ID=TWW.SUBSCRIPTION_ID where TSM.order_type='SELF_REGISTRATION'),
Set Event_ID=(Select 10101 from TEMP_WF_WORKFLOW TWW JOIN TMP_SOAP_MONITORING_IDS TSM ON TSM.SUBSCRIPTION_ID=TWW.SUBSCRIPTION_ID where TSM.order_type='NEW')

Upvotes: 0

Views: 3244

Answers (1)

jarlh
jarlh

Reputation: 44766

Update TEMP_WF_WORKFLOW
  Set Event_ID= case (select TSM.order_type
                      from TEMP_WF_WORKFLOW TWW JOIN TMP_SOAP_MONITORING_IDS TSM
                        ON TSM.SUBSCRIPTION_ID=TWW.SUBSCRIPTION_ID)
                  when 'SELF_REGISTRATION' then 10008
                  when 'NEW' then 10003
                  when 'SELF_REGISTRATION' then 10078
                  when 'NEW' then 10101
                  else Event_ID
                end

This is what you're trying to do! Note that same values are repeated!!!

Why hardcoded integer values?

Upvotes: 3

Related Questions