Home » SQL & PL/SQL » SQL & PL/SQL » 3 updates fail, 1 does not. Why?
icon5.gif  3 updates fail, 1 does not. Why? [message #242386] Fri, 01 June 2007 15:06 Go to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
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!
Re: 3 updates fail, 1 does not. Why? [message #242388 is a reply to message #242386] Fri, 01 June 2007 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and apply How to format your posts
Please post your Oracle version (4 decimals).
Please copy and paste your execution instead of wording it.

Regards
Michel
Re: 3 updates fail, 1 does not. Why? [message #242393 is a reply to message #242386] Fri, 01 June 2007 15:24 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Sorry about that. Let me know if this is sufficient.

We are running Oracle 10.2.0.1

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
)

2560 row(s) affected.


Re: 3 updates fail, 1 does not. Why? [message #242396 is a reply to message #242393] Fri, 01 June 2007 15:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use:
update
(select p.net_head, p.gen_avail, p.pump_avail,
        p.obj_time_ref_id, otr.obj_time_ref_id
 from ps_plant_procng_detl p, 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)
set net_head = 99, gen_avail = 599, pump_avail = 199
/

Regards
Michel
Re: 3 updates fail, 1 does not. Why? [message #242397 is a reply to message #242386] Fri, 01 June 2007 15:43 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Michel,

Your code produced an error:

ORA-00904: "PS_PLANT_PROCNG_DETL"."OBJ_TIME_REF_ID": invalid identifier

I was really looking for an answer to why the 1st 3 queries failed with:

ORA-00933: SQL command not properly ended

Thanks.
Re: 3 updates fail, 1 does not. Why? [message #242399 is a reply to message #242397] Fri, 01 June 2007 15:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ just change ps_plant_procng_detl.obj_time_ref_id to p.obj_time_ref_id.
2/ Your first 3 updates does not work because it is Sybase synatx and neither an Oracle nor ANSI one.

Regards
Michel
Re: 3 updates fail, 1 does not. Why? [message #242400 is a reply to message #242386] Fri, 01 June 2007 15:50 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>I was really looking for an answer to why the 1st 3 queries failed with:

The failed because each contained INVALID syntax for the UPDATE statement.

For example, "ps_plant_procng_detl.obj_time_ref_id" is WRONG for your topmost attempt. You simplay can NOT arbitrarily use/include table_name.column_name in the WHERE clause.
Re: 3 updates fail, 1 does not. Why? [message #242401 is a reply to message #242386] Fri, 01 June 2007 16:07 Go to previous messageGo to next message
tomstone_98
Messages: 44
Registered: May 2007
Location: Atlanta
Member
Thank you very much.

I see the syntax explanation at:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm

I am very intrigued with what I refer to as the "inline" query in your SQL. Apparently, Oracle allows you to update the result set of the inline query. What would happen if I tried to also SET the value of a column in the 2nd table (obj_time_ref)? ... i.e. ...

    set net_head = 99, gen_avail = 599, pump_avail = 199, otr_column = 42

?
Re: 3 updates fail, 1 does not. Why? [message #242429 is a reply to message #242401] Sat, 02 June 2007 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't. You can only update one table. Oracle send you an error (just try it).
Updating an inline view/query is part of SQL standard.
Restriction is that they must be a one to one relationship (that is here obj_time_ref_id is the PK) so the update is deterministic.

Regards
Michel
Re: 3 updates fail, 1 does not. Why? [message #242630 is a reply to message #242386] Mon, 04 June 2007 08:02 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Syntax and invalid references aside, all of the queries are failing because you did not end them with a forward slash on a separate line or a semicolon.
Previous Topic: how to indentify the coloum
Next Topic: Rownum
Goto Forum:
  


Current Time: Wed Dec 07 14:50:31 CST 2016

Total time taken to generate the page: 0.10952 seconds