53 lines
1.9 KiB
PL/PgSQL
53 lines
1.9 KiB
PL/PgSQL
-- P5-4876 migrate order billing address
|
|
|
|
BEGIN;
|
|
|
|
INSERT INTO "order-fields" (name) VALUES ('billing_address_id');
|
|
|
|
-- temporary column with reference to the order
|
|
ALTER TABLE addresses ADD COLUMN order_id INTEGER;
|
|
|
|
-- copy billing address
|
|
INSERT INTO addresses (
|
|
order_id,
|
|
address1,
|
|
address2,
|
|
company_name,
|
|
country_code,
|
|
city,
|
|
zipcode,
|
|
first_name,
|
|
last_name,
|
|
state
|
|
)
|
|
SELECT
|
|
orders.orderid,
|
|
address1.value,
|
|
address2.value,
|
|
company.value,
|
|
country.value,
|
|
city.value,
|
|
zipcode.value,
|
|
firstname.value,
|
|
lastname.value,
|
|
state.value
|
|
FROM "order-orders" orders
|
|
LEFT JOIN "order-orders_fields" address1 ON address1.orderid = orders.orderid AND address1.fieldid = 158
|
|
LEFT JOIN "order-orders_fields" address2 ON address2.orderid = orders.orderid AND address2.fieldid = 252
|
|
LEFT JOIN "order-orders_fields" company ON company.orderid = orders.orderid AND company.fieldid = 177
|
|
LEFT JOIN "order-orders_fields" country ON country.orderid = orders.orderid AND country.fieldid = 151
|
|
LEFT JOIN "order-orders_fields" city ON city.orderid = orders.orderid AND city.fieldid = 160
|
|
LEFT JOIN "order-orders_fields" zipcode ON zipcode.orderid = orders.orderid AND zipcode.fieldid = 159
|
|
LEFT JOIN "order-orders_fields" firstname ON firstname.orderid = orders.orderid AND firstname.fieldid = 156
|
|
LEFT JOIN "order-orders_fields" lastname ON lastname.orderid = orders.orderid AND lastname.fieldid = 157
|
|
LEFT JOIN "order-orders_fields" state ON state.orderid = orders.orderid AND state.fieldid = 235;
|
|
|
|
-- save billing address id on the order
|
|
INSERT INTO "order-orders_fields" (orderid, fieldid, value)
|
|
SELECT order_id, 255, id FROM addresses WHERE order_id IS NOT NULL;
|
|
|
|
-- drop temp column
|
|
ALTER TABLE addresses DROP COLUMN order_id;
|
|
|
|
COMMIT;
|