27 lines
855 B
MySQL
27 lines
855 B
MySQL
|
WITH bmc_owner AS (
|
||
|
|
SELECT MIN(id) AS id
|
||
|
|
FROM customers
|
||
|
|
WHERE lower(name) = 'bmc networks'
|
||
|
|
AND is_active = true
|
||
|
|
),
|
||
|
|
candidate_connections AS (
|
||
|
|
SELECT DISTINCT ic.id
|
||
|
|
FROM internet_connections_connections ic
|
||
|
|
JOIN internet_connections_ip_ranges ir
|
||
|
|
ON ir.connection_id = ic.id
|
||
|
|
AND ir.deleted_at IS NULL
|
||
|
|
WHERE ic.deleted_at IS NULL
|
||
|
|
AND ic.parent_id IS NULL
|
||
|
|
AND ic.subscription_id IS NULL
|
||
|
|
AND ic.provider ILIKE 'GlobalConnect%'
|
||
|
|
AND ir.customer_id IS NULL
|
||
|
|
AND COALESCE(ic.address, '') <> ''
|
||
|
|
)
|
||
|
|
UPDATE internet_connections_connections ic
|
||
|
|
SET customer_id = COALESCE((SELECT id FROM bmc_owner), ic.customer_id),
|
||
|
|
allocation_model = 'shared',
|
||
|
|
value_type = 'bmc_networks',
|
||
|
|
value_label = NULL,
|
||
|
|
updated_at = CURRENT_TIMESTAMP
|
||
|
|
WHERE ic.id IN (SELECT id FROM candidate_connections);
|