1

How can I configure the default address in magento via SQL.

I have over 7000 users, however some of them are not defined.

Each client has ~2 registered address, I would like that the two addresses were marked one as the default billing and another shipping.

Via SQL there is some command?

The default could be the first one found, or the first encountered.

https://i.sstatic.net/8Cjtn.jpg

4
  • What do you mean by "default address"? Can you give an example? Assigning a fixed street address to each customer does not sound useful to me. Sep 1, 2015 at 14:00
  • Each client has ~2 registered address, I would like that the two addresses were marked one as the default billing and another shipping. Like the image i.imgur.com/aV6onTe.jpg Sep 1, 2015 at 14:02
  • You can set default billing and default shipping to an existing customer address via SQL. But by which logic would you determine which of them is the billing address and which the shipping address? Sep 1, 2015 at 14:06
  • It could be the first one found, or the first encountered. Sep 1, 2015 at 14:09

1 Answer 1

2

Disclaimer: Please make a backup of your database and/or test on a copy before trying out the query!

To set the first found customer address as default shipping address, use

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

Then repeat with 'default_shipping' replaced by 'default_billing' in both places.

This will probably set the same address as default for both but since it is an arbitrary choice anyway I did not go the extra mile to write a second query that compares the address against the assigned shipping address.

Updated with tested query

12
  • Did not work. )): 0 row (s) inserted. (A query took 0.0356 seconds.) Sep 1, 2015 at 16:39
  • Please try to change the last inner join to a left join. If that doesn't work I'll have to test.. Sep 1, 2015 at 16:43
  • Now return: #1452 - Cannot add or update a child row: a foreign key constraint fails (database.customer_entity_int, CONSTRAINT FK_CSTR_ENTT_INT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID FOREIGN KEY (entity_type_id) REFERENCES eav_entity_type (entity_type_id) Sep 1, 2015 at 16:49
  • fschmengler, please, could you check your code? Sep 2, 2015 at 11:26
  • Answer updated. Sep 2, 2015 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.