Home » SQL & PL/SQL » SQL & PL/SQL » Update Statement Using Join (Oracle10g)
Update Statement Using Join [message #306133] Thu, 13 March 2008 00:50 Go to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,

Can any one guide me how to handle this in oracle

This is in SQL SERVER2005
*****************************

Table Name : A

SELECT *FROM A;

rolno dept comm
---------------------------------
1 10 null
2 20 null


Table Name : B

SELECT *FROM B;

dept comm
----------------------
10 200
20 300
30 600


UPDATE A SET COMM = Z.COMM
FROM A X JOIN B Z
ON X.DEPT = Z.DEPT



SELECT *FROM A;

rolno dept comm
---------------------------------
1 10 200
2 20 300

This is in Oracle10g R2
******************************

SQL> select *from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


SQL> select *from con;

DEPTNO COMM
---------- ----------
10 200
20 500

SQL> select comm,deptno
2 from emp1
3 where deptno in (10,20);

COMM DEPTNO
---------- ----------
20
20
10
20
10
20
20
10

8 rows selected.

SQL> update emp1
2 set comm = b.comm
3 from emp1 e join con b
4 on (e.deptno=b.deptno);

from emp1 e join con b
*
ERROR at line 3:
ORA-00933: SQL command not properly ended


SQL> ed
Wrote file afiedt.buf

1 update emp1
2 set comm = b.comm
3 from emp1 e ,con b
4* where e.deptno=b.deptno

SQL> /
from emp1 e ,con b
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

SQL> ed
Wrote file afiedt.buf

1 update (select a.deptno empdept,a.comm empcomm,b.comm concom,b.deptno condept
2 from emp a,con b
3 where a.deptno=b.deptno) z
4 set
5 z.empcomm = z.concom
6* where z.empdept = z.condept
SQL> /
z.empcomm = z.concom
*
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Thanks,
Thani.
Re: Update Statement Using Join [message #306137 is a reply to message #306133] Thu, 13 March 2008 01:00 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
The very first post in the forum tells you how to format your post so better go thru it.Does this work for you
update ... set <col_name>=
(select <col_name> from <tab_name1> ,<tab_name2>
...<join_condition>)



regards,
Re: Update Statement Using Join [message #306143 is a reply to message #306133] Thu, 13 March 2008 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also for syntax error, you can refer to SQL Reference.
For instance, UPDATE

Regards
Michel
Re: Update Statement Using Join [message #306144 is a reply to message #306137] Thu, 13 March 2008 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, here's the truth: SQL Server <> Oracle. Didn't know that? What a shame! Now that you do, perhaps you'll be interesting in bookmarking Oracle documentation and search for correct syntax (UPDATE) after spending much too much time to make Oracle do something it is incapable of.
Re: Update Statement Using Join [message #306146 is a reply to message #306137] Thu, 13 March 2008 01:15 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi,

SQL> ed
Wrote file afiedt.buf

1 update emp1
2 set comm = (select c.comm
3 from con c,emp1 e
4* where e.deptno = c.deptno)

5 /
set comm = (select c.comm
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

Thanks,
Thani.
Re: Update Statement Using Join [message #306158 is a reply to message #306133] Thu, 13 March 2008 01:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
update emp1
set comm = (select c.comm
from con c, EMPL E
where e.deptno = c.deptno)
/

Have a look at the tablename in uppercase. Ther inner query returns COMMs for all EMPLs, as E.DEPTNO references to the table in uppercase.
What about removing it (the tablename in uppercase)? Then E.DEPTNO would reference to the updated table. Of course, DEPTNO shall be unique in COMM table; I suppose it is so in your case.

Also, please, use CODE tags, as described in http://www.orafaq.com/forum/t/88153/0/, section "How to format your post?".
Re: Update Statement Using Join [message #306159 is a reply to message #306146] Thu, 13 March 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Update Statement Using Join [message #306160 is a reply to message #306133] Thu, 13 March 2008 01:34 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
http://www.orafaq.com/forum/t/98657/92734/

regards,
Re: Update Statement Using Join [message #306162 is a reply to message #306159] Thu, 13 March 2008 01:37 Go to previous message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Thanks Michel.
Previous Topic: Joining with huge tables?
Next Topic: sql help
Goto Forum:
  


Current Time: Fri Dec 02 22:58:34 CST 2016

Total time taken to generate the page: 0.51302 seconds