asked on

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.

slightwv (䄆 Netminder)

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

cobolinx1

ASKER

That's what I assumed. there is no way to run an update command with a WITH clause?

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cobolinx1

ASKER

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???

cobolinx1

ASKER

I had to switch the to an in and it works great thanks!