Skip to content

Instantly share code, notes, and snippets.

@andyparsons
Created March 11, 2016 19:16
Show Gist options
  • Save andyparsons/b9aaca53403075f0cfdc to your computer and use it in GitHub Desktop.
Save andyparsons/b9aaca53403075f0cfdc to your computer and use it in GitHub Desktop.
-- firms
select 'UPDATE users SET product_manufacturer_id = ' || firm_id || ' WHERE id = ' || user_id || ';' FROM (
(SELECT id AS user_id, company_id, email, (SELECT SUBSTRING(email FROM POSITION('@' IN email)+1)) AS domain FROM users) u
JOIN
(SELECT id AS firm_id, email, url, (SELECT SUBSTRING(email FROM POSITION('@' IN email)+1)) AS domain FROM companies) f ON u.domain = f.domain)
WHERE f.domain != '' AND
company_id IS NOT NULL AND
f.domain != 'kontor.com' AND
f.domain != 'gmail.com'
ORDER BY user_id;
-- brands
SELECT 'UPDATE users SET product_manufacturer_id = ' || brand_id || ' WHERE id = ' || user_id || ';' FROM (
(SELECT id AS user_id, product_manufacturer_id, email, (SELECT SUBSTRING(email FROM POSITION('@' IN email)+1)) AS domain FROM users) u
JOIN
(SELECT id AS brand_id, email, website_url, (SELECT SUBSTRING(email FROM POSITION('@' IN email)+1)) AS domain FROM product_manufacturers) b ON u.domain = b.domain)
WHERE b.domain != '' AND
product_manufacturer_id IS NOT NULL AND
b.domain != 'kontor.com' AND
b.domain != 'gmail.com'
ORDER BY user_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment