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

Question

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')

You know, we don't know the structure of your database out here in internet-land. How are we supposed to know what tables and columns are incorrect? — Gordon Linoff, Mar 01 '15 at 12:53
Ohh i understand i will change the query little bit and i just want to the syntax error in my query — Andrew, Mar 01 '15 at 12:54
SET keyword should be only before first column. Why same column over and over again? — jarlh, Mar 01 '15 at 13:00
Its because the conditions are different and i want to update the column based on those conditions — Andrew, Mar 01 '15 at 13:03
just for first select can you give me an example please using CASE — Andrew, Mar 01 '15 at 13:05
i cannot use CASE because i have join condition also right ? — Andrew, Mar 01 '15 at 13:14
You had the case expression working in your previous question http://stackoverflow.com/questions/28558692/missing-keyword-error-in-oracle-case-when-sql-statement, so use similar `CASE` expression for update statement too. For example, http://stackoverflow.com/a/28581240/3989608 — Lalit Kumar B, Mar 01 '15 at 13:27

jarlh · Accepted Answer · 2015-03-01T13:29:51.103

3

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?

edited Mar 01 '15 at 13:29

answered Mar 01 '15 at 13:14

jarlh

42,561
8
45
63

Without hardcoding the value how can i update the EVENT_ID column with the values using the condition ? – Andrew Mar 01 '15 at 13:36
@Rahul, jarlh is suggesting there ought to be a table where values of `EVENT_ID` correspond to the `ORDER_TYPE` – David Faber Mar 01 '15 at 13:37
No there are too many events with the same order type and i dont have separate table for this – Andrew Mar 01 '15 at 13:39
@DavidFaber, exactly! Make the query independent of the current set of "codes". – jarlh Mar 01 '15 at 13:40
Here is the query i tried but gives an error missing parenthesis :- Update TEMP_WF_WORKFLOW Set Event_ID= case (select TSM.order_type,WF.STATUS_ID,WF.NAME from TEMP_WF_WORKFLOW TWW JOIN TMP_SOAP_MONITORING_IDS TSM ON TSM.SUBSCRIPTION_ID=TWW.SUBSCRIPTION_ID) when ('SELF_REGISTRATION',0,'SIGNUP_MOBILE_PRE_PAID') then 10008 END – Andrew Mar 01 '15 at 13:41
@Jarlh sorry to mention in my query that i have 2 more conditions and when i try to use it it gives an error missing parenthesis – Andrew Mar 01 '15 at 13:43
You mean 'SELF_REGISTRATION',0,'SIGNUP_MOBILE_PRE_PAID') shall all have 10008? One "when / then " for each type is needed. I.e. when 'SELF_REGISTRATION' then 10008 when 0 then 10008 when etc. But I still think this is the wrong way to go, pick code from another table instead! – jarlh Mar 01 '15 at 13:44
I don't think I've seen a tuple used in a `CASE` before. Not sure it would work. – David Faber Mar 01 '15 at 23:11

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

1 Answers1