Home » SQL & PL/SQL » SQL & PL/SQL » Multi Table Update / Join syntax not working (10g)
Multi Table Update / Join syntax not working [message #327450] Mon, 16 June 2008 08:03 Go to next message
bogey
Messages: 53
Registered: February 2006
Member
I've run this query against my MS SQL database and was able to get it to work. When we try against our Oracle 10g I'm getting a ORA-0093: SQL command not properly ended. Can anyone see where the error is. Thanks

UPDATE A
SET A.EMPLOYEE_KEY = S.EMPLOYEE_KEY
FROM SP_FACT_SIT_ACTUAL A
INNER JOIN
(
SELECT a.employee_key,
a.person_id
FROM base_employee a,
( SELECT MAX(BASE_EMPLOYEE.TIME_ADDED_FIS) AS TIME_ADDED_FIS,
BASE_EMPLOYEE.PERSON_ID
FROM BASE_EMPLOYEE
INNER JOIN SP_FACT_SIT_ACTUAL ON BASE_EMPLOYEE.PERSON_ID = SP_FACT_SIT_ACTUAL.PERSON_ID
AND BASE_EMPLOYEE.TIME_ADDED_FIS <= SP_FACT_SIT_ACTUAL.TIME_ADDED_FIS
GROUP BY BASE_EMPLOYEE.PERSON_ID
) c
WHERE a.person_id = c.person_id) S
ON S.PERSON_ID = A.PERSON_ID

Re: Multi Table Update / Join syntax not working [message #327452 is a reply to message #327450] Mon, 16 June 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a supported syntax in Oracle.
See SQL Reference, UPDATE

Regards
Michel
Re: Multi Table Update / Join syntax not working [message #327472 is a reply to message #327450] Mon, 16 June 2008 08:52 Go to previous messageGo to next message
jaganerp@gmail.com
Messages: 63
Registered: April 2008
Member
u have write you are query like this in you are query you are not mention select statement
update emp set empno=(
select d.empno from emp d inner join(
SELECT ename,a.empno FROM emp a,
(select e.empno,max(sal) as time_id,comm ,e.deptno from emp e inner join dept d on e.deptno=d.deptno and e.sal<4000
group by comm,e.deptno,e.empno) b WHERE a.empno = b.empno)f on d.empno=f.empno)
Re: Multi Table Update / Join syntax not working [message #327473 is a reply to message #327472] Mon, 16 June 2008 08:54 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@jaganerp@gmail.com

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.

Why do you still refuse to follow the rules?

Regards
Michel
Previous Topic: Alter table constraint help
Next Topic: synonym priority
Goto Forum:
  


Current Time: Sat Dec 10 22:41:14 CST 2016

Total time taken to generate the page: 0.08158 seconds