Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: 3 updates fail, 1 does not. Why?

    Why do the first 3 updates below fail with:
    ORA-00933: SQL command not properly ended
    and the last one does not?

    I am coming from the Sybase world so this probably an Oracle syntax issue that I need to learn.

    Thanks.


    update ps_plant_procng_detl
    set net_head = 99
    ,gen_avail = 599
    ,pump_avail = 199
    from obj_time_ref otr
    where ps_plant_procng_detl.obj_time_ref_id = otr.obj_time_ref_id
    and to_number( to_char( otr.obj_time_ref, 'mi' ) ) = 50
    -- 1 6 1 ORA-00933: SQL command not properly ended

    update ps_plant_procng_detl
    set net_head = 99
    ,gen_avail = 599
    ,pump_avail = 199
    join obj_time_ref otr
    on ps_plant_procng_detl.obj_time_ref_id = otr.obj_time_ref_id
    and to_number( to_char( otr.obj_time_ref, 'mi' ) ) = 50
    -- 1 6 1 ORA-00933: SQL command not properly ended

    update ps_plant_procng_detl
    set net_head = 99
    ,gen_avail = 599
    ,pump_avail = 199
    join obj_time_ref
    using (obj_time_ref_id)
    and to_number( to_char( obj_time_ref, 'mi' ) ) = 50
    -- 1 6 1 ORA-00933: SQL command not properly ended


    update ps_plant_procng_detl
    set net_head = 99
    ,gen_avail = 599
    ,pump_avail = 199
    where exists
    ( select 1
    from obj_time_ref otr
    where ps_plant_procng_detl.obj_time_ref_id = otr.obj_time_ref_id
    and to_number( to_char( otr.obj_time_ref, 'mi' ) ) = 50
    )
    -- Works!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, it is about incorrect syntax. Check the UPDATE statement here.

    The last one works because the syntax is correct. Obviously.

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    Thanks. I see the error of my ways now. I saved the URL as a favorite.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •