1 Replies - 1069 Views - Last Post: 01 October 2014 - 12:15 PM

#1 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 592
  • View blog
  • Posts: 3,823
  • Joined: 12-January 10

the bane that is oracle error

Posted 01 October 2014 - 09:20 AM

UPDATE PON_ELEM_INSP
   SET ELEM_KEY = 146 
   from PON_ELEM_INSP
 inner join bridge on bridge.brkey = PON_ELEM_INSP.brkey 
 WHERE PON_ELEM_INSP.brkey in (select bridge.brkey from bridge 
 inner join PON_ELEM_INSP on bridge.brkey = PON_ELEM_INSP.brkey
 where 
 BRIDGE.DESIGNMAIN = '11' OR BRIDGE.DESIGNMAIN = '12')
 and 
 PON_ELEM_INSP.ELEM_KEY = 135
 and 
 bridge.brkey = PON_ELEM_INSP.brkey;


the above works in sql however in oracle it throws this error

Quote

Error at Command Line : 2 Column : 23
Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"


any thoughts on what could cause this?

Is This A Good Question/Topic? 0
  • +

Replies To: the bane that is oracle error

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: the bane that is oracle error

Posted 01 October 2014 - 12:15 PM

Hmm...
UPDATE PON_ELEM_INSP
    SET ELEM_KEY = 146 
    from PON_ELEM_INSP
        inner join bridge
            on bridge.brkey = PON_ELEM_INSP.brkey 
    WHERE PON_ELEM_INSP.brkey in (
        select bridge.brkey
            from bridge 
                inner join PON_ELEM_INSP
                    on bridge.brkey = PON_ELEM_INSP.brkey
            where BRIDGE.DESIGNMAIN = '11' OR BRIDGE.DESIGNMAIN = '12'
       )
        and PON_ELEM_INSP.ELEM_KEY = 135
        and bridge.brkey = PON_ELEM_INSP.brkey;



I'm guessing you're coming from an MS SQL. I'm afraid the Oracle UPDATE doesn't support that non standard update ... from stuff.

That WHERE IN is nasty. Actually, that whole update could use a little TLC:
UPDATE PON_ELEM_INSP
    SET ELEM_KEY = 146 
    from PON_ELEM_INSP a
        inner join bridge b
            on b.brkey = a.brkey 
                and b.DESIGNMAIN in ('11','12')
    where a.ELEM_KEY = 135



Unless I'm reading that wrong, the above should work in MS SQL, but not Oracle.

In Oracle you pretty much have to brute force it. And, generally, you do have to use a nasty WHERE IN.

e.g.
UPDATE PON_ELEM_INSP
    SET ELEM_KEY = 146 
    where brkey in (
        select distinct a.brkey 
            from PON_ELEM_INSP a
                inner join bridge b
                    on b.brkey = a.brkey 
                        and b.DESIGNMAIN in ('11','12')
            where a.ELEM_KEY = 135
        )



Hope this helps.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1