 update using with statement [message #647828] |
Tue, 09 February 2016 16:16  |
 |
fhsmagna
Messages: 3
Registered: February 2016
Location: USA
|
Junior Member |
|
|
Having problem with an update statement using a with clause.
with cte as (select b1,b2,b3,b4 from table2)
update table a
set(a1,a2,a3)=
(select b1,b2,b3,b4 from cte
where b4 = a.a4
and b3 <> a.a3);
When I run this statement I get: Error ORA-00928: missing SELECT keyword
I can run the SELECT statement inside the parentheses for the cte with no errors.
The select statement for the cte is a lot more complex.
What am I doing wrong?
Thanks,
|
|
|
|
| Re: update using with statement [message #647829 is a reply to message #647828] |
Tue, 09 February 2016 17:06  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
update table a
set(a1,a2,a3)=
(with cte as (select b1,b2,b3,b4 from table2)
select b1,b2,b3,b4 from cte
where b4 = a.a4
and b3 <> a.a3);
SY.
|
|
|
|
|
|
|
|
| Re: update using with statement [message #647875 is a reply to message #647872] |
Wed, 10 February 2016 09:28 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Use SQL*Plus and copy and paste your session, the WHOLE session including object creation statements.
Note that:
set(a1,a2,a3)
select b1,b2,b3,b4
clearly show a discrepancy.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
