Home » SQL & PL/SQL » SQL & PL/SQL » updating null column
updating null column [message #229762] Mon, 09 April 2007 23:42 Go to next message
white
Messages: 13
Registered: October 2006
Junior Member

I have two tables let us say

a. l is first table contains the data as

select * from l;

C1 C2
----- ----------
10 20
20 30
30 20

b. l1 is another table contains data as

select * from l1;

C1 C2 C3
----- ---------- ----------
1 10
2 20
3 30

SO,here we have to update the column c3 in the table l1
where the condition is:
first it compares the columns c1 in l and c2 in l1 and retrieve the value of c2 in l.
i.e
sql>select l.c2 from l,l1 where l.c1=l1.c2;

and then we need to retrive the repective vlaues of c1 in l1 table from the above query.
i.e
SQL>select l1.c1 from l1 where c2 in(select l.c2 from l,l1 where l.c1=l1.c2)

up to this i am gettin correct...
but i need to update the column c3 as the values of l1.c1
i.e
select * from l1;

C1 C2 C3
----- ---------- ----------
1 10 2
2 20 3
3 30

this is what i want the output.

Advanced Thanks,
Re: updating null column [message #229793 is a reply to message #229762] Tue, 10 April 2007 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If we follow your requirements and data we get:
SQL> update l1 a
  2  set c3 = ( select b.c1 from l1 b, l
  3             where l.c1 = a.c2
  4               and l.c2 = b.c2
  5           )
  6  /

3 rows updated.

SQL> select * from l1;
        C1         C2         C3
---------- ---------- ----------
         1         10          2
         2         20          3
         3         30          2

3 rows selected.

I don't see how you could get NULL in the last row as your algorithm is 30->20->2.

Regards
Michel

[Updated on: Tue, 10 April 2007 01:32]

Report message to a moderator

Re: updating null column [message #229903 is a reply to message #229793] Tue, 10 April 2007 05:04 Go to previous message
white
Messages: 13
Registered: October 2006
Junior Member
thank u very much for immediate response
Previous Topic: Delete Duplicates
Next Topic: Merging two columns..
Goto Forum:
  


Current Time: Mon Dec 05 09:15:22 CST 2016

Total time taken to generate the page: 0.19426 seconds