| 36439 |
amit |
1 |
-- Migration: Add outlet_name to fofo.fofo_store and backfill from user.address
|
|
|
2 |
-- Run during low-traffic window (fofo_store is heavily queried, ALTER takes metadata lock)
|
|
|
3 |
|
|
|
4 |
-- Step 1: Add column
|
|
|
5 |
ALTER TABLE fofo.fofo_store ADD COLUMN outlet_name VARCHAR(255) DEFAULT NULL AFTER gst_number;
|
|
|
6 |
|
|
|
7 |
-- Step 2: Backfill from address.name via retailer_registered_address
|
|
|
8 |
-- Uses MAX(rra.address_id) to handle the 3 stores with multiple address entries
|
|
|
9 |
UPDATE fofo.fofo_store fs
|
|
|
10 |
JOIN (
|
|
|
11 |
SELECT rra.retailer_id, a.name
|
|
|
12 |
FROM dtr.retailer_registered_address rra
|
|
|
13 |
JOIN user.address a ON a.id = rra.address_id
|
|
|
14 |
WHERE rra.address_id = (
|
|
|
15 |
SELECT MAX(rra2.address_id)
|
|
|
16 |
FROM dtr.retailer_registered_address rra2
|
|
|
17 |
WHERE rra2.retailer_id = rra.retailer_id
|
|
|
18 |
)
|
|
|
19 |
) src ON src.retailer_id = fs.id
|
|
|
20 |
SET fs.outlet_name = src.name;
|
|
|
21 |
|
|
|
22 |
-- Step 3: Verify
|
|
|
23 |
SELECT COUNT(*) AS total, SUM(outlet_name IS NOT NULL) AS migrated FROM fofo.fofo_store;
|