0

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.

http://i.imgur.com/aV6onTe.jpg

  • 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. – Fabian Schmengler Sep 1 '15 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 – user2925795 Sep 1 '15 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? – Fabian Schmengler Sep 1 '15 at 14:06
  • It could be the first one found, or the first encountered. – user2925795 Sep 1 '15 at 14:09
1

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

| improve this answer | |
  • Did not work. )): 0 row (s) inserted. (A query took 0.0356 seconds.) – user2925795 Sep 1 '15 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.. – Fabian Schmengler Sep 1 '15 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) – user2925795 Sep 1 '15 at 16:49
  • fschmengler, please, could you check your code? – user2925795 Sep 2 '15 at 11:26
  • Answer updated. – Fabian Schmengler Sep 2 '15 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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