update a column with another table column in sql with the same column being the id

Question

I am trying to update columnA in tableA. The new values of ColumnA is extracted from tableB ColumnB, using column a as the ID. I am using the following queries, but i am unable to update the table.

update tableA a set columnA = (select b.columnB from tableb b where b.columnC = a.columnA) 
where exists (select * from tableb b where b.columnC = a.columnA) and a.columnD = 'ABC'

For the above query i am getting the exception 'Single Row subquery returns more than one row'

update tableA a set a.columnA = b.columnB from tableb b on a.columnA = b.columnC where a.columnD = 'ABC'

For the above query i am getting the exception 'SQL command not properly ended'

update a set a.columnA = b.columnB from tablea a inner join tableb b on a.columnA=b.columnC where a.columnD = 'ABC'

For the above query i am getting the exception 'SQL command not properly ended'

Answer 1

I think your issue is that you have multiple rows in tableB that match ("where b.columnC = a.columnA"). So when you tell Oracle to:

set columnA = (
select b.columnB 
from tableb b 
where b.columnC = a.columnA)
where exists ...

its finding multiple rows in tableB for a given key value. You'll have to decide how you'd like Oracle to choose just one. For example, if you really don't care which of the multiple values, you could do something like (untested):

set columnA = (
select distinct(max(b.columnB)) 
from tableb b 
where b.columnC = a.columnA)
where exists ...