CODEWITHSUNDEEP
sqloracle-database
Tarun J
Tarun J

Reputation: 17

Update a table foreign keys by substituting by a lookup from the result of another table

Suppose I have two tables :

Table 1:

SELECT * FROM ORIGINAL_DEALER;

    ID ENAME

----- --------------------------
    1 JOHN
    2 00000JOHN
    3 JACK
    4 00000JACK
    5 MARIA
    6 00000MARIA

Table 2:

SELECT * FROM NAMES;

ID_NUM
------
 1
 2
 3
 4
 5
 6
  1. I'll have to update NAMES.
  2. Table ORIGINAL_DEALER has duplicate / invalid names such as 00000JOHN The invalid names have exactly five zeros prefixed before the valid names. i.e valid name: JOHN invalid name: 00000JOHN
  3. Now I'll have to select the ID's of invalid names from ORIGINAL_DEALER table and update the ID_NUM in NAMES table WITH VALID ID. i.e when the invalid ename= '00000JOHN' has an ID = 2 which is also invalid. but original_dealer is parent table, i'll have to replace all the invalid id's in NAMES table with valid ones.

i.e the output after updating NAMES should be:

SELECT * FROM NAMES;

ID_NUM
------
 1
 1
 3
 3
 5
 5

How can I do it without manually setting it everytime as there will huge data? I'm using Oracle DB

Upvotes: 0

Views: 68

Answers (1)

StuartLC
StuartLC

Reputation: 107277

You can use a update statement with a lookup like below, by using an inner lookup subquery which in turn uses the TRIM function to strip out the leading zeroes during matching. We also restrict the update to just those Names linked to OriginalDealer records starting with 00000 to mimimize the risk of unnecessary updates.

UPDATE Names SET ID_NUM = 
(SELECT od1.ID
 FROM OriginalDealer od1
      INNER JOIN OriginalDealer od2
      ON od1.EName = TRIM(LEADING '0' FROM od2.EName)
 WHERE od2.ID = Names.ID_NUM)
WHERE EXISTS
(
  SELECT 1
  FROM OriginalDealer od
  WHERE od.ENAME LIKE '00000%'
  AND Names.ID_NUM =  od.ID
);

SqlFiddle here

Note that the model of marking duplicates with a prefix like '00000' is rather fragile:

  • It assumes there is exactly 1 match between the 00000NAME and NAME rows in ORIGINAL_DEALER
  • If the above isn't true, it will attempt to set Names.ID_NUM to NULL or Fail if there is more than one match (which is probably a good thing, we don't want to corrupt data)

Upvotes: 1

Related Questions