Updating a column in a table basing on the data from another table

[pavan_teja]

I had a small issue.
Can you pls check this...

I have two tables TABLEA,TABLEB.

TABLEA
--------------
col1 col2 col3

TABLEB
--------------
cl1 cl2 cl3

If col2 in TABLEA = cl2 in TABLEB,
then i need to update cl3 in TABLE B with col3 in TABLE A.

I tried running following queries but didnt work out.

QUERY1:
update TABLEB set b.cl3=a.col3 from TABLEA a,TABLEB b
where a.col2=b.cl2;

QUERY2:
update TABLEB set cl3=".cl3" where cl2 in
(select col2 as ".cl2",col3 as ".cl3" from TABLEA)

Can you suggest some way to proceed further.

[NeverLift]

Unworthy of a post. You need basic Oracle training.

[pavan_teja]

Unworthy of a post. You need basic Oracle training.

Hello mister..
did you see wat i asked first..

If u dont know just keep quiet...

i dont need ur words of wisdom..

[robertmacedonia]

This is not the place for insulting or fighting. The best way for you to do this is probably using PL/SQL, like with procedures,triggers etc... Do you have any knowledge of PL/SQL? If not I suggest you read some basic tutorial, like this one - http://www.plsql-tutorial.com/ I do not know whether this problem is a part of a schoolwork, project or something else, but I guess you can always ask your teachers about a little help with PL/SQL, I guess they'll be surprised to see that you went a little further with your interests, and will be happy to help you.

[levine]

The issue can be solved via PL/SQL.

[NeverLift]

PL/SQL, bah! This is simple stuff, y'all:

update tableb set cl3 = (select col3 from tablea where col2=cl2);

The only issue is: what will happen if the nested select returns multiple rows?
Answer: Run time error:
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

Well,

update tableb b set cl3 = (select col3 from tablea where col2=cl2 and rownum=1);

Works just fine.

This is really elemewntary stuff. Get the complete Oracle docs (mine goes back awhile, everything on one CD), start reading. And run experiments, try stuff out. Or follow robertmacedonia advice.

To be a little snotty about it: Then come back with real issues. This is a forum to help with problems, not for teaching elementary Oracle sql. Same comment applies to those who couldn't give the answer. "The issue can be solved via PL/SQL" indeed!

[NeverLift]

Wrong. My bad. SQL I published is incorrect, it'll change every row in tableb. Blush.

Sorry about that.

[NeverLift]

OK, this should do it. It updates only rows in tableb that have entries in tablea:

update tableb set cl3=(select col3 from tablea where col2=cl2 and rownum=1) where cl2 in (select col2 from tablea);