Update with predicate

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 !

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.

/

Code:

---

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

---

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 ?

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

- Chris

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

Code:

---

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

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

You can also try this.

Code:

---

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

---

Thanks a lot too ...