Doing an update with an oracle with clause?

I have a physical table that I'm populating with employee data except for three columns. I have the sql to determine how to populate the three columns but I'm using a with clause. How do I update the three columns in my table using a with clause in Oracle? I have several differant with clauses to update these three columns based on various data.
cobolinx1Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Updates are similar.

given the example above
update tab2 set (col2, col3) =
(
select col2,col3 from (
with myTab as ( select 'a' col1, 'Z' col2, 'X' col3 from dual )
select col1,col2,col3 from myTab where col1='a'
)
)
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
You didn't provide a lot of information so I had to do some assuming.

See if this test case will help you
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1), col3 char(1));

insert into tab1 values('a','b','c');
insert into tab1 values('b','c','d');
commit;

drop table tab2 purge;
create table tab2(col1 char(1), col2 char(1), col3 char(1));

insert into tab2 
(
select col1, col2,col3 from (
with myTab as ( select 'a' col1, '1' col2, '2' col3 from dual )
select col1,col2,col3 from myTab where col1='a'
)
)
/

Open in new window

0
 
cobolinx1Author Commented:
That's what I assumed. there is no way to run an update command with a WITH clause?
0
 
cobolinx1Author Commented:
Ok almost there I want to do something like this


update tab2 set col4 = 'TEST' where  (col2, col3) in
(
select col2,col3 from (
with myTab as ( select 'a' col1, 'Z' col2, 'X' col3 from dual )
select col1,col2,col3 from myTab where col1='a'
)
)

I get a single row subqury returns more then one row
how would I do that???
0
 
cobolinx1Author Commented:
I had to switch the to an in and it works great thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.