Oracle SQL updating different record in same table-CodePudding

I have searched and can't find this use case. I have a table with phone and fax numbers that I need to use to update other records in that same table based on a field in one record matching a different field in a different record.

Table named Company_Info
company_ID, parent_company_ID, bank_acct
ABC             DEF             <null>  
DEF             <null>          555444333
GHI             <null>          123456789
MNO             GHI             <null>  
PQR             DEF             <null>

I am trying to update the bank acct numbers of the parent company to the sub-company. For example, bank acct from DEF is 555444333 so I need to update company ABC with that bank acct info, etc. I have thousands of records in the table.

I can get all of the relevant info into the same temp table like this:

  `SELECT subno,parentco,acct,ci.company_ID,ci.bank_acct`  
  `FROM Company_Info CI`
  `INNER JOIN` 
  `(SELECT company_id as subno, parent_compapny_id as parentco, bank_acct as acct`
  `FROM Company_Info`
  `ON ci.company_id = parentco`

resulting temp table is:

subno,parentco,acct,company_ID,bank_acct
ABC    DEF    <null>   DEF     555444333
MNO    GHI    <null>   GHI     123456789
PQR    DEF    <null>   DEF     555444333

My desired end result is for Company_Info table to look like this with bank acct of the parent company populated for all records where bank acct is currently null:

company_ID, parent_company_ID, bank_acct
ABC             DEF             555444333  
DEF             <null>          555444333
GHI             <null>          123456789
MNO             GHI             123456789
PQR             DEF             555444333

I've tried other iterations of WHERE EXISTS statements etc but for the life of me cannot figure out the right combo to update the table with the bank acct info for a company_id with the info from its parent company. I always get invalid sql. I am stuck and would appreciate any help. I don't believe this is a hard solution but I don't have the knowledge to get it completed. Thanks.

CodePudding user response：

If there is only one level of parent-child relationships (as seems to be the case here), I think you can just do:

UPDATE Company_Info c1
SET bank_acct = (SELECT bank_acct FROM Company_Info c2 WHERE  c2.company_id = c1.parent_company_id)
WHERE bank_acct IS NULL

Anywhere the bank_acct is currently null, this will try to find the parent company and use the bank_acct from there. It doesn't matter if a parent company doesn't exist or has a null bank_acct since it will only update ones that are currently null (updating null to null won't matter).