57 lines
7.4 KiB
SQL
57 lines
7.4 KiB
SQL
-- Update some Gelato product ids
|
|
|
|
CREATE TEMP TABLE temp_external_id_changes (current_id TEXT NOT NULL, new_id TEXT NOT NULL);
|
|
|
|
INSERT INTO temp_external_id_changes (current_id, new_id) VALUES
|
|
('framed_poster_300x300-mm-12x12-inch_white_wood_w12xt22-mm_plexiglass_300x300-mm-12x12-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_300x300-mm-12x12-inch_white_wood_w20xt20-mm_plexiglass_300x300-mm-12x12-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_300x400-mm-12x16-inch_white_wood_w12xt22-mm_plexiglass_300x400-mm-12x16-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_300x400-mm-12x16-inch_white_wood_w20xt20-mm_plexiglass_300x400-mm-12x16-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_300x450-mm-12x18-inch_white_wood_w12xt22-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_400x500-mm-16x20-inch_white_wood_w12xt22-mm_plexiglass_400x500-mm-16x20-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_400x500-mm-16x20-inch_white_wood_w20xt20-mm_plexiglass_400x500-mm-16x20-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_450x600-mm-18x24-inch_white_wood_w12xt22-mm_plexiglass_450x600-mm-18x24-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_450x600-mm-18x24-inch_white_wood_w20xt20-mm_plexiglass_450x600-mm-18x24-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_500x500-mm-20x20-inch_white_wood_w12xt22-mm_plexiglass_500x500-mm-20x20-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_500x500-mm-20x20-inch_white_wood_w20xt20-mm_plexiglass_500x500-mm-20x20-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_500x700-mm-20x28-inch_white_wood_w12xt22-mm_plexiglass_500x700-mm-20x28-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_500x700-mm-20x28-inch_white_wood_w20xt20-mm_plexiglass_500x700-mm-20x28-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_600x800-mm-24x32-inch_white_wood_w12xt22-mm_plexiglass_600x800-mm-24x32-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_600x800-mm-24x32-inch_white_wood_w20xt20-mm_plexiglass_600x800-mm-24x32-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_600x900-mm-24x36-inch_white_wood_w12xt22-mm_plexiglass_600x900-mm-24x36-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_600x900-mm-24x36-inch_white_wood_w20xt20-mm_plexiglass_600x900-mm-24x36-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_700x700-mm-28x28-inch_white_wood_w12xt22-mm_plexiglass_700x700-mm-28x28-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_700x700-mm-28x28-inch_white_wood_w20xt20-mm_plexiglass_700x700-mm-28x28-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_700x1000-mm-28x40-inch_white_wood_w12xt22-mm_plexiglass_700x1000-mm-28x40-inch_200-gsm-80lb-uncoated_4-0_ver', 'framed_poster_premium_700x1000-mm-28x40-inch_white_wood_w20xt20-mm_plexiglass_700x1000-mm-28x40-inch_200-gsm-80lb-uncoated_4-0_ver'),
|
|
('framed_poster_300x400-mm-12x16-inch_white_wood_w12xt22-mm_plexiglass_300x400-mm-12x16-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_300x400-mm-12x16-inch_white_wood_w20xt20-mm_plexiglass_300x400-mm-12x16-inch_200-gsm-80lb-uncoated_4-0_hor'),
|
|
('framed_poster_300x450-mm-12x18-inch_white_wood_w12xt22-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_hor'),
|
|
('framed_poster_400x500-mm-16x20-inch_white_wood_w12xt22-mm_plexiglass_400x500-mm-16x20-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_400x500-mm-16x20-inch_white_wood_w20xt20-mm_plexiglass_400x500-mm-16x20-inch_200-gsm-80lb-uncoated_4-0_hor'),
|
|
('framed_poster_450x600-mm-18x24-inch_white_wood_w12xt22-mm_plexiglass_450x600-mm-18x24-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_450x600-mm-18x24-inch_white_wood_w20xt20-mm_plexiglass_450x600-mm-18x24-inch_200-gsm-80lb-uncoated_4-0_hor'),
|
|
('framed_poster_500x700-mm-20x28-inch_white_wood_w12xt22-mm_plexiglass_500x700-mm-20x28-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_500x700-mm-20x28-inch_white_wood_w20xt20-mm_plexiglass_500x700-mm-20x28-inch_200-gsm-80lb-uncoated_4-0_hor'),
|
|
('framed_poster_600x800-mm-24x32-inch_white_wood_w12xt22-mm_plexiglass_600x800-mm-24x32-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_600x800-mm-24x32-inch_white_wood_w20xt20-mm_plexiglass_600x800-mm-24x32-inch_200-gsm-80lb-uncoated_4-0_hor'),
|
|
('framed_poster_600x900-mm-24x36-inch_white_wood_w12xt22-mm_plexiglass_600x900-mm-24x36-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_600x900-mm-24x36-inch_white_wood_w20xt20-mm_plexiglass_600x900-mm-24x36-inch_200-gsm-80lb-uncoated_4-0_hor'),
|
|
('framed_poster_700x1000-mm-28x40-inch_white_wood_w12xt22-mm_plexiglass_700x1000-mm-28x40-inch_200-gsm-80lb-uncoated_4-0_hor', 'framed_poster_premium_700x1000-mm-28x40-inch_white_wood_w20xt20-mm_plexiglass_700x1000-mm-28x40-inch_200-gsm-80lb-uncoated_4-0_hor');
|
|
|
|
-- Temporarily drop FK constraint
|
|
ALTER TABLE prices_external_products DROP CONSTRAINT prices_external_products_external_product_id_fkey;
|
|
|
|
-- First update the prices_external_products values
|
|
UPDATE prices_external_products
|
|
SET external_product_id = temp_external_id_changes.new_id
|
|
FROM temp_external_id_changes
|
|
WHERE prices_external_products.external_product_id = temp_external_id_changes.current_id;
|
|
|
|
-- Then external_print_products. We reset the stock status as well, as it is a different product
|
|
UPDATE external_print_products
|
|
SET external_product_id = temp_external_id_changes.new_id, stock_status = DEFAULT
|
|
FROM temp_external_id_changes
|
|
WHERE external_print_products.external_product_id = temp_external_id_changes.current_id;
|
|
|
|
-- Re-add the FK constraint
|
|
ALTER TABLE prices_external_products ADD CONSTRAINT prices_external_products_external_product_id_fkey
|
|
FOREIGN KEY (external_product_id) REFERENCES external_print_products (external_product_id);
|
|
|
|
--- Set 300x450-mm to out of stock for all but EU
|
|
INSERT INTO external_print_products_out_of_stock VALUES
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_hor', 'AS'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_hor', 'SA'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_hor', 'OC'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_hor', 'ROW'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_hor', 'US-CA'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_ver', 'AS'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_ver', 'SA'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_ver', 'OC'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_ver', 'ROW'),
|
|
('framed_poster_premium_300x450-mm-12x18-inch_white_wood_w20xt20-mm_plexiglass_300x450-mm-12x18-inch_200-gsm-80lb-uncoated_4-0_ver', 'US-CA');
|