| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: What is wrong with my SQL ?
A copy of this was sent to lars.kristiansen_at_bergen.online.nospam (Lars
Kristiansen)
(if that email address didn't require changing)
On Fri, 26 Mar 1999 12:38:24 GMT, you wrote:
>On Fri, 26 Mar 1999 13:10:42 +0100, "Jørgen Haukland"
><jorgen.hauikland_at_fou.telenor.no> wrote:
>
>>Hello !
>>
>>I'm trying to update a column in my main table with values from a similar
>>column in another table when the primary keys column match. This is my SQL:
>>
>> UPDATE TableA
>> SET TableA.Col1 = TableB.Col1
>> WHERE TableA.PrimaryKeyCol = TableB.PrimaryKeyCol
>>
>>I get an "invalid column name" error whenn I try this.
>>
>>What is wrong ?
>>
>>Joergen Haukland
>>NORWAY
>>
>You should try something like this :
>UPDATE TabelA SET TabelA.Col1 = (SELECT TableB.Col FROM TableB WHERE
>TableB.PrimaryKeyCol = TabelA.PrimaryKeyCol)
>
be careful with that one -- if TableA has primarykeyCol values that are NOT in tableB, those rows in TableA will have their col1 set to NULL.
Try:
UPDATE TabelA SET TabelA.Col1 = (SELECT TableB.Col FROM TableB WHERE TableB.PrimaryKeyCol = TabelA.PrimaryKeyCol) where exists ( select null from tableb where tableb.primaryKeyCol = tablea.primaryKeyCol )
or
UPDATE TabelA SET TabelA.Col1 = (SELECT TableB.Col FROM TableB WHERE TableB.PrimaryKeyCol = TabelA.PrimaryKeyCol) where tablea.primaryKeyCol in ( select primaryKeyCol from tableb )
>
>Lars Kristiansen
>email: lars.kristiansen_at_bergen.online.nospam
>~ Remove spam to reply ~
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |