Home » SQL & PL/SQL » SQL & PL/SQL » update one table and compare with another table (9i)
update one table and compare with another table [message #377833] Thu, 25 December 2008 19:18 Go to next message
rakeshforum
Messages: 5
Registered: September 2008
Location: india
Junior Member
hi,
There are two tables A and B and have same columns and same data type.
   A                     B
  ----                -------
 c1  c2 c3           c1 c2 c3


these two table contain same records (also duplicate) but two columns c1,c2 in A table have some fields contain different data.
my requirement is that what ever data present in c1 , c2 column of table B , i want to update that data on table A
for example:
  A                      B
----                    -------
c1 c2 c3               c1 c2 c3
1  a  1                1  a   1 
5  s  2                2  t   2
5  y  3                2  n   3
6  p  4                3  p   4

what ever records present in table B, update that also in A.
i mean A and B have same records and data.


[mod-edit: added code tags; next time please add them yourself]

[Updated on: Thu, 25 December 2008 20:19] by Moderator

Report message to a moderator

Re: update one table and compare with another table [message #377846 is a reply to message #377833] Thu, 25 December 2008 22:19 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You mean to say update with respect to column c3 ?

What did you try so far ?

Smile
Rajuvan.
Re: update one table and compare with another table [message #377885 is a reply to message #377833] Fri, 26 December 2008 01:28 Go to previous messageGo to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Update t1
Set t1.C1 = (Select t2.c1
From t2
Where Exists t1.c3 = t2.c3),
t1.C2 = (Select t2.c2
From t2
Where Exists t1.c3 = t2.c3)
/

Update t2
Set t2.C1 = (Select t1.c1
From t1
Where Exists t1.c3 = t2.c3),
t2.C2 = (Select t1.c2
From t1
Where Exists t1.c3 = t2.c3)
/

Try this...
Re: update one table and compare with another table [message #377889 is a reply to message #377885] Fri, 26 December 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sathyam2627,

1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.

2/
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


3/
Try to put something that is at least syntaxically correct.
SQL> select * from emp where exists 1=1;
select * from emp where exists 1=1
                               *
ERROR at line 1:
ORA-00906: missing left parenthesis

Regards
Michel
Re: update one table and compare with another table [message #377913 is a reply to message #377833] Fri, 26 December 2008 04:13 Go to previous messageGo to next message
sathyam2627
Messages: 52
Registered: November 2006
Member
Sorry, Syntax Was wrong
Update t1
Set t1.C1 = (Select t2.c1
From t2
Where t1.c3 = t2.c3),
t1.C2 = (Select t2.c2
From t2
Where t1.c3 = t2.c3)
/

Update t2
Set t2.C1 = (Select t1.c1
From t1
Where t1.c3 = t2.c3),
t2.C2 = (Select t1.c2
From t1
Where t1.c3 = t2.c3)
/
Re: update one table and compare with another table [message #377914 is a reply to message #377889] Fri, 26 December 2008 04:15 Go to previous message
sathyam2627
Messages: 52
Registered: November 2006
Member
Michel,

Thanks for your suggestions.
Previous Topic: How to use Bulk Collect with dbms_sql.varchar2a in 10g
Next Topic: Cursors
Goto Forum:
  


Current Time: Sun Dec 04 12:33:44 CST 2016

Total time taken to generate the page: 0.07453 seconds