gistfile1.txt

-- 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;