CODEWITHSUNDEEP
sqloracle-databasesql-update
Zelda
Zelda

Reputation: 1

Oracle update using multiple columns without unique key

I will start by saying I am a novice. I have two tables (locations and assets). The location table has fac_id/bldg/location_code/loc_id and the asset table has fac_id/bldg/location_code/loc_id. I need to update the asset table loc_id with the loc_id from the location table where the loc_id is null in the asset table. However, the fac_id/bldg/location_code need to be matched up in both tables to update the correct loc_id. I have a script that I continue to get errors on. I have tried many different tweaks from internet research (join, inner join, merge, where exist, etc...) and nothing is working. On this original script I get either a too many rows error or single row subquery returns more than one row. Hopefully the solution is something simple that I just don't know.

I appreciate any help, my head hurts from hitting it against the wall.

UPDATE asset b 
SET b.loc_id = (SELECT DISTINCT l.fac_id, l.bldg, l.location_code, l.loc_id 
                FROM location l 
                WHERE upper(trim(b.fac_id))=upper(trim(l.fac_id)) 
                     AND upper(trim(b.bldg))=upper(trim(l.bldg)) 
                   AND upper(trim(b.location_code))=upper(trim(l.location_code)) 
                ) 
WHERE b.loc_id IS NULL;

Thank you!

Upvotes: 0

Views: 181

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

The simplest solution is and rownum = 1:

UPDATE asset b 
    SET b.loc_id = (SELECT l.loc_id 
                    FROM location l 
                    WHERE upper(trim(b.fac_id)) = upper(trim(l.fac_id)) AND
                          upper(trim(b.bldg)) = upper(trim(l.bldg)) AND
                          upper(trim(b.location_code)) = upper(trim(l.location_code)) AND
                          rownum = 1
                    ) 
    WHERE b.loc_id IS NULL;

However, this may not be a "real" solution. You should figure out why thee are multiple matching rows. Perhaps something is wrong with your correlation condition or with your logic.

Upvotes: 0

Related Questions