Repair missing store_id s on orders in Magento (1)

If you delete a store in Magento 1 you lose the store_id reference. Which is a problem, especially if you want to migrate the data to Shopware. Assuming you didn't delete the store - or you recreated the store with exactly the same website, store group and store names you can recover the store_id with

SELECT
	s.store_id
FROM
	core_store s
WHERE
	s.name = TRIM(BOTH '\n' FROM SUBSTRING(o.store_name, LOCATE("\n", o.store_name, LOCATE("\n", o.store_name, LOCATE("\n", o.store_name) + 1))));

UPDATE
	sales_flat_order o
SET
	store_id = (
		SELECT
			store_id
		FROM
			core_store s
			INNER JOIN core_store_group g ON s.group_id = g.group_id
			INNER JOIN core_website w ON w.website_id = g.website_id
		WHERE
			o.store_name = TRIM(BOTH '\n' FROM SUBSTRING(o.store_name, LOCATE("\n", o.store_name, LOCATE("\n", o.store_name, LOCATE("\n", o.store_name) + 1))))
			AND g. `name` = TRIM(BOTH '\n' FROM SUBSTRING(o.store_name, LOCATE("\n", o.store_name), LOCATE("\n", o.store_name, LOCATE("\n", o.store_name) + 2) - LOCATE("\n", o.store_name)))
			AND w. `name` = TRIM(BOTH '\n' FROM SUBSTRING(o.store_name, 1, LOCATE("\n", o.store_name))))
	WHERE
		o.store_id IS NULL
--		AND o.entity_id = 2866;