0

I have a situation where I am doing a data fix from back up.

Table MAIN TABLE (PrimaryKey, Value) and Table BACKUP(PRIMARYKEY, Value).

I want to find all the records in MAIN Table with value=0 , then go fetch the value for the same primary key from table BACKUP and update the MAIN Table.

  1. There are 20 millions records with value=0
  2. Updates and fetch are both done using primary key

Questions

  1. Stored procedure? Script?

  2. Fetch and update are done on the same table? Any concerns?

  3. How much time do you think it will take- ball park figure. how to test?

Solution I was thinking :

Open a cursor on Table Main with my condition(value=0) and then go fetch value from BACKUP and then update. Commit every 10K updates in a loop

Any thoughts?

APC
  • 144,005
  • 19
  • 170
  • 281
Marco
  • 83
  • 1
  • 10

2 Answers2

1

You can give a try to Oracle's MERGE.

Make sure you make tests in test tables before applying the query to main tables.

MERGE INTO main_table m
USING backup_table b
ON (m.primary_key = b.primary_key)
WHEN MATCHED THEN
    UPDATE SET m.value = b.value 
    WHERE m.value = 0;
Ergi Nushi
  • 837
  • 1
  • 6
  • 17
  • Can we wait till this is run completely before we commit? that seems to be a lot of data? – Marco Dec 12 '19 at 16:01
  • I have used this query on a table with 10M rows and I don't remember it taking a lot of time to execute. I waited until the end and then committed. – Ergi Nushi Dec 12 '19 at 16:05
  • Give me a ballpark time? Like under 30 mins? – Marco Dec 12 '19 at 16:21
  • My estimation is under 10 minutes. I can say under 5 minutes but I'm not sure about this. – Ergi Nushi Dec 12 '19 at 16:35
  • Thakns for the input. I will take the above Merge query approach and keep you posted.I am also reading about suggestions like : Enable Parallel DML and Force Parallel DML Parallel 16. Any thoughts? – Marco Dec 12 '19 at 20:14
  • I haven't used any of them so I cannot give a proper suggestion. But since they can enable parallel execution, queries might become faster. I think that they might increase resource usage though, but as i said at the beginning. – Ergi Nushi Dec 12 '19 at 20:37
-1
UPDATE MAIN_TABLE 
SET main.value=back.value
FROM MAIN_TABLE as main
JOIN BACKUP_TABLE as back ON main.pk=back.pk
WHERE main.value=0

Here is where I found how to do this: https://chartio.com/resources/tutorials/how-to-update-from-select-in-sql-server/

Tyler H
  • 110
  • 9