| Update help [message #1627] |
Thu, 16 May 2002 11:59  |
Arthur
Messages: 9
Registered: May 2002
|
Junior Member |
|
|
I need to update one table from the value of a second table where a field in table one matches a field in table two. I have tried using this statement:
Update table1 set field_x = table2.field_y
from table1, table2 where table1.field1 = table2.field1
It does not work. Oracle does not seem to support update with inner joining tables. (I hope I am wrong). I have used a subquery to do the update:
Update table1 set field_x = (select field_y from table2
where table2.field1 = table1.field1)
The problem with this statement is it updates field_x in table1 with null if there is no match in table2 when the value of field_x should remain unchanged where there is no match.
If there is more than one field to update, I need to write a subquery for each field that requires updating from table2, like
set field_m = (select field_n ....), field_j = (select field_k ...), field_a = (select....)
Can anyone help me with this update?
Thanks.
|
|
|
|
| Figured it out, but find another problem [message #1632 is a reply to message #1627] |
Thu, 16 May 2002 14:08  |
Arthur
Messages: 9
Registered: May 2002
|
Junior Member |
|
|
I used an update statement like this:
Update table1 set
field_x = (select field_y from table2 where table2.field1 = table1.field1)
where field1 In (select field1 from table2)
It works fine.
But where table1 and table2 have two joining fields, how to write the where part of the update statement?
Update table1 set
field_x = ( select field_y from table2
where table2.field1 = table1.field1
and table2.field2 = table1.field2 )
where ????
|
|
|
|
| Re: Figured it out, but find another problem [message #1633 is a reply to message #1632] |
Thu, 16 May 2002 14:26 |
Phenoracle
Messages: 35
Registered: March 2001
|
Member |
|
|
Hi,
Try this
UPDATE table1
SET field_x =
(
SELECT field_y
FROM table2
WHERE table2.field1 = table1.field1
AND table2.field2 = table1.field2
)
WHERE EXISTS
(
SELECT NULL
FROM table2 t2
WHERE t2.field1 = table1.field1
AND t2.field2 = table1.field2
)
Have fun
Phenom
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
