Update with predicate

[htanga]

Hi all !
I would like to update a table with a particular select like the following :

select c1,c2,c3 from tab1;
c1 c2 c3
-- -- --
1 10 100
2 10 100
3 10 100

select c1,c2,c3 from tab2;
c1 c2 c3
-- -- --
10 100
10 100
10 100
1 20 200

I would like to update c1 column in tab2 with the value of tab1 to obtain :

select c1,c2,c3 from tab2;
c1 c2 c3
-- -- --
1 10 100
2 10 100
3 10 100
1 20 200

Is there a simple way to do that with one update ?
Thanks a lot in advance !

[patel_dil]

Not sure how you would do it in one SQL for all the records but if you dont mind to putting this in PL/SQL loop, the following SQL might help.

/

update tab2
set c1 = ( select c1 from tab1 where c1 not in ( select c1 from tab2  where c1 is not null)
and rownum = 1)
where c1 is null
and rownum = 1

[htanga]

Thanks for the reply !
I'm so sorry ... I've forgot to say that c2 and c3 columns are the primary key in both tables.
So I have to join the two tables on these key ...
Any idea ?

[chrisrlong]

Then you need a better example, because c2+c3 can't be the PKs given your data.

- Chris

[htanga]

Sorry ...

select c1,c2,c3 from tab1;
c1 c2 c3
-- -- --
1 117 A
2 185 D
3 169 Z

select c1,c2,c3 from tab2;
c1 c2 c3
-- -- --
117 A
185 D
169 Z
1 220 Q

[chrisrlong]

UPDATE
   T2
SET
   C1 =
      (
      SELECT
         C1
      FROM
         T1
      WHERE
         T1.C2 = T2.C2 AND
         T1.C3 = T2.C3
      )
WHERE
   EXISTS
      (
      SELECT
         1
      FROM
         T1
      WHERE
         T1.C2 = T2.C2 AND
         T1.C3 = T2.C3
      )

You may be able to directly update an in-line view as well. I'll leave that to someone else to show. I hate Oracle's implementation of that so bad that I never use it.

Welcome to Oracle

- Chris

[htanga]

Thanks a lot Chris !
It's exactly what I need ... I miss the "where exists" part !
Best regards !

[patel_dil]

You can also try this.

 update tab2 set c1 = (select c1 from tab1 where c2 = tab2.c2 and c3 = tab2.c3) 
 where c1 is null;

[htanga]

Thanks a lot too ...