Search code examples
sqloracle-databasesql-updatesubquery

Oracle set value in column when associated value is found in another table

I have two tables - referrals and bankruptcy.

Referrals

Referralno | bk_filed
----------------------
123        |
456        |
789        |

Bankruptcy

Referralno 
----------
875
123
789

I would like populate the bk_filed column with a "Y" if the referralno number matches in both tables. Results would look like this.

Referralno | bk_filed
----------------------
123        | Y
456        |
789        | Y

I've tried the following merge statement but can't get it to work.

merge into LCTS.referrals r
using LCTS.bankruptcy b
on (r.referralno = b.referralno )
when matched then update
set r.bk_filed = "Y"
Solution

  • You can write this as an update statement, using exists and a correlated subquery for filtering:

    update referrals r
set bk_filed = 'Y'
where exists (select 1 from bankruptancy b wehre b.referralno = r.referralno)