update table with another table [message #20545] |
Tue, 04 June 2002 04:29 |
Anke
Messages: 8 Registered: November 1999
|
Junior Member |
|
|
I have two tables
table a
ID RW HW
1 10 11
2 12 12
3 11 15
4 14 14
table b
NR X Y
1
2 12 12
3
4 14 14
I have to update table b with the table a.
The result should be
table b
NR X Y
1 10 11
2 12 12
3 11 15
4 14 14
Who can help me?
|
|
|
Re: update table with another table [message #20546 is a reply to message #20545] |
Tue, 04 June 2002 05:46 |
Sujit Mahapatra
Messages: 19 Registered: March 2002
|
Junior Member |
|
|
The following should work:
UPDATE B TABLE_2
SET TABLE_2.X=(SELECT TABLE_1.RW FROM
A TABLE_1
WHERE TABLE_1.ID = TABLE_2.NR),
TABLE_2.Y=(SELECT TABLE_1.HW FROM
A TABLE_1
WHERE TABLE_1.ID = TABLE_2.NR);
This would work at the simplest cases.
But there may be some problems, if there are
no matching rows found in both tables, I have
not taken care of. Also it may not be the best
optimized piece of code. In case of a problem,
do revert back..
Cheers,
Sujit
|
|
|
Re: update table with another table [message #20549 is a reply to message #20545] |
Tue, 04 June 2002 07:09 |
sridhar
Messages: 119 Registered: December 2001
|
Senior Member |
|
|
Try this, please post the feed back,
UPDATE table_b b
SET b.X = NVL((SELECT a.RW from table_a a
WHERE a.ID = b.NR), b.X),
b.Y = NVL((SELECT a.HW from table_a a
WHERE a.ID = b.NR), b.Y);
Thx,
SriDHAR
|
|
|
|