Home » SQL & PL/SQL » SQL & PL/SQL » trying to update table 1 but int he where clauses it is joining two tables (Oracle 9)
trying to update table 1 but int he where clauses it is joining two tables [message #286950] Mon, 10 December 2007 12:01 Go to next message
jonathan184
Messages: 21
Registered: November 2006
Junior Member

Hi I am trying to update a field in table 1 but in the where clause it is joining two tables which i want.

But i get this message when trying to run the query

ORA-00971: missing SET keyword

UPDATE exception e, exception_detail ed
SET ed.userrole ='Published'
where e.componentname = 'FTL_Inbound'
--and trunc(e.created_date) = trunc(sysdate-3)
and e.globaltransid = 3
and ed.erroractivity = 'SOAPRequestReply'



Could somebody help me out please.
Re: trying to update table 1 but int he where clauses it is joining two tables [message #286951 is a reply to message #286950] Mon, 10 December 2007 12:07 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
UPDATE can only work against a single table at a time.
Re: trying to update table 1 but int he where clauses it is joining two tables [message #286953 is a reply to message #286951] Mon, 10 December 2007 12:14 Go to previous messageGo to next message
jonathan184
Messages: 21
Registered: November 2006
Junior Member

ok i tired it this way but it is telling me the sql statement has not ended properly

UPDATE exception_detail ed
SET ed.userrole ='Published'
from  exception e, exception_detail ed
where e.componentname = 'FTL_Inbound'
--and trunc(e.created_date) = trunc(sysdate-3)
and e.globaltransid = 2
and ed.erroractivity = 'SOAPRequestReply'
Re: trying to update table 1 but int he where clauses it is joining two tables [message #286954 is a reply to message #286950] Mon, 10 December 2007 12:19 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
It appears you are incapable or unwilling to actually read & FOLLOW the SQL Reference manual found at http://tahiti.oracle.com

The UPDATE statement does NOT contain any FROM clause
Re: trying to update table 1 but int he where clauses it is joining two tables [message #286957 is a reply to message #286950] Mon, 10 December 2007 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UPDATE
MERGE

Regards
Michel
Re: trying to update table 1 but int he where clauses it is joining two tables [message #286971 is a reply to message #286950] Mon, 10 December 2007 14:08 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

in the where clauses it is joining two tables
Maybe you just did not post whole query, but I do not see any JOIN condition between E and ED, just cartesian join with some WHERE conditions on single table.

By the way, you may easily search this forum to get an idea:
Difficult update from sub query SQL statement
Re: update query performance
pl/sql anonymous block
... and another cca 270 posts
Re: trying to update table 1 but int he where clauses it is joining two tables [message #287001 is a reply to message #286971] Mon, 10 December 2007 19:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You might also look into Updateable Join Views here and here

Ross Leishman
Re: trying to update table 1 but int he where clauses it is joining two tables [message #287022 is a reply to message #286950] Mon, 10 December 2007 23:20 Go to previous message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

from  exception e, exception_detail ed


Don't do like above. You can refer below query. It may helpful and even i don't find any join condition between e and ed.

UPDATE tab1 a
   SET a.col1 = 'ABC'
 WHERE a.col2 = 100
 AND a.col3 IN (SELECT b.col2
                FROM tab2 b
                WHERE b.col3 = 15
                AND b.col4 = 'XYZ')



Kiran.
Previous Topic: multiple self join
Next Topic: help~~problem of Trigger
Goto Forum:
  


Current Time: Wed Dec 07 16:36:27 CST 2016

Total time taken to generate the page: 0.08163 seconds