Set stored address as default for Shipping or Billing in Magento

A current project I’m working on with a client required to import 20k customers with address, contacts and profile data – a tricky task in itself. I managed it import this successfully, but noted that the addresses were set to Shipping and not Billing.  This meant that customers could not be searched or filtered from in the Magento admin columns as the filters use the billing address for queries.

As my client relies on searching through 20k records by postcode, street name etc it needed to be done. I though for a while about how best to do this, and decided the best route was to set the Magento address as the default. In the backend, there was a setting to enable this for each customer:

Now there was no way I was going to set this for 20k+ customers, so I needed a way to do this in the database in one single swoop.  This is how I did it:

Set customers address as the default Shipping:

INSERT INTO customer_entity_int (entity_id, entity_type_id, attribute_id, value)
  SELECT
    e.entity_id,
    1,
    (SELECT
      attribute_id
    FROM eav_attribute
    WHERE attribute_code = 'default_shipping'),
    address.entity_id
  FROM customer_entity e
  LEFT JOIN customer_entity_int v
    ON v.entity_id = e.entity_id
    AND v.attribute_id = (SELECT
      attribute_id
    FROM eav_attribute
    WHERE attribute_code = 'default_shipping')
  INNER JOIN customer_address_entity address
    ON address.parent_id = e.entity_id
  WHERE value_id IS NULL
  GROUP BY e.entity_id

Set customers address as the default Billing:

INSERT INTO customer_entity_int (entity_id, entity_type_id, attribute_id, value)
  SELECT
    e.entity_id,
    1,
    (SELECT
      attribute_id
    FROM eav_attribute
    WHERE attribute_code = 'billing_shipping'),
    address.entity_id
  FROM customer_entity e
  LEFT JOIN customer_entity_int v
    ON v.entity_id = e.entity_id
    AND v.attribute_id = (SELECT
      attribute_id
    FROM eav_attribute
    WHERE attribute_code = 'billing_shipping')
  INNER JOIN customer_address_entity address
    ON address.parent_id = e.entity_id
  WHERE value_id IS NULL
  GROUP BY e.entity_id

Just note that you must be inside your database to action this, and if you have changed table names from the default, you will need to update the above to set the correct prefix to get it to work.  MySQL will certainly tell you in an error if this needs to be done.

Backup before doing anything and hope this helps!