Organize source data into CSV: STORE_CODE,STORE_TYPE,OLD_AREA,NEW_AREA
Use a simple Python template to generate bulk output:
import csv, datetime
tpl = "update site_store set area = {new} where store_code = '{code}' and store_type = '{typ}';"
with open('mapping.csv') as f, open('update.sql','w') as out:
for row in csv.DictReader(f):
out.write(tpl.format(**row)+'\n')
Template advantages:
High readability; future maintenance only requires CSV changes
Supports adding other fields (like city) without modifying script logic
Pre-Execution Validation
Backup:
Use CREATE TABLE site_store_bak AS SELECT * FROM site_store WHERE store_code IN (...) to back up affected rows
Constant checks:
Verify SELECT COUNT(1) FROM site_store_bak matches script line count
Validate encoding: confirm all new_area values exist in the sys_area reference table
Best Execution Practices
Session setup:
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE
Execute via @update.sql and stop on error
Batch commits:
COMMIT every 500 rows to minimize rollback scope
Verification and Rollback
Quick validation:
Run SELECT COUNT(*) WHERE area = new_area to verify success rate
Aggregate comparison: SUM(old_area) vs SUM(new_area)
Rollback plan: if incorrect updates occur, use MERGE to restore the backup table to the original
Common Pitfalls
Row lock contention: the store table experiences writes from other processes; schedule updates during off-peak windows
Incorrect encoding: a typo in the WHERE clause could update stores nationwide; always test in staging and validate the execution plan with EXPLAIN PLAN
Permission issues: production execution requires DBA assistance; complete change control procedures in advance
Optimization and Extension
Use MERGE INTO to complete the operation in a single step:
MERGE INTO site_store s USING tmp_area_map m
ON (s.store_code = m.store_code AND s.store_type = m.store_type)
WHEN MATCHED THEN UPDATE SET s.area = m.new_area;
Add CHECK(area IN (SELECT id FROM sys_area)) to prevent corrupt data
Automate validation: trigger a Jenkins job after execution to compare anomalies and generate alerts
Summary
Bulk data scripts require reliable script generation and comprehensive backup and validation
Single UPDATE statements work well for small scenarios; consider MERGE or ETL tools for large scale
Apply data constraints at the source to reduce downstream correction costs