1

We were having this issue

PayPal Express FIRSTNAME and LASTNAME instead of SHIPTONAME

Now we have many accounts where there is no firstname and secondlastname, however shipping data is correctly entered

How can we update the DB and fix these Empty firstname and secondlastname issues using the data available in billing or shipping address fields?

thx

2
  • What Magento version do you use, 1.9? What data do you want to update exactly? The customer firtsname / lastname in customer accounts or are also orders affected?
    – HelgeB
    Mar 3, 2019 at 8:15
  • Accounts that are created
    – snh_nl
    Mar 3, 2019 at 8:53

1 Answer 1

1

Based on your very brief description of your situation I assume that ALL orders have the columns customer_firstname and customer_lastname in sales_flat_orderfilled correctly. Otherwise those fields must be updated first from sales_flat_order_address. If that's necessary I can provide you a statement for that too.

With that given we have two possible situations (which matches for you can't be known from what you have posted in your question): There are entries for your customer accounts in the customer_entity_varchar table with NULL or empty value. In this case we need to update those rows. The first two statements are for this situation. The second situation is that you don't have any entry at all for firstname or lastname in the customer_entity_varchar table. In this case we need to insert the data. Please note that if you have more orders for one customer_id and different names (what technically can happen) the first matching data will be taken.

Feel free to use this statements. I would recommend you to test them first in an appropriate test / stage system since I don't know your customizations. On standard 1.9 Magento they work.

-- update customer firstname if there is a null entry in the varchar table
update customer_entity_varchar firstname
join sales_flat_order o on firstname.entity_id = o.customer_id and firstname.attribute_id = (select attribute_id from eav_attribute where attribute_code = 'firstname' and entity_type_id = 1)
set firstname.value = o.customer_firstname
where firstname.value is NULL OR firstname.value = '';

-- update customer lastname if there is a null entry in the varchar table
update customer_entity_varchar lastname
join sales_flat_order o on lastname.entity_id = o.customer_id and lastname.attribute_id = (select attribute_id from eav_attribute where attribute_code = 'lastname' and entity_type_id = 1)
set lastname.value = o.customer_lastname
where lastname.value is NULL OR lastname.value = '';


-- insert customer firstname values in varchar tables if there is no data at all for the customer that table
insert into customer_entity_varchar 
(entity_type_id , attribute_id, entity_id, value)
select 1, (select attribute_id from eav_attribute where attribute_code = 'firstname' and entity_type_id = 1), o.customer_id, o.customer_firstname
from
(select customer_id, customer_firstname from sales_flat_order group by customer_id) o 
join customer_entity c on c.entity_id = o.customer_id
left outer join customer_entity_varchar firstname on firstname.entity_id =  c.entity_id and firstname.attribute_id = (select attribute_id from eav_attribute where attribute_code = 'firstname' and entity_type_id = 1)
where firstname.value is null;

-- insert customer lastname values in varchar tables if there is no data at all for the customer that table
insert into customer_entity_varchar 
(entity_type_id , attribute_id, entity_id, value)
select 1, (select attribute_id from eav_attribute where attribute_code = 'lastname' and entity_type_id = 1), o.customer_id, o.customer_firstname
from
(select customer_id, customer_firstname from sales_flat_order group by customer_id) o 
join customer_entity c on c.entity_id = o.customer_id
left outer join customer_entity_varchar lastname on lastname.entity_id =  c.entity_id and lastname.attribute_id = (select attribute_id from eav_attribute where attribute_code = 'lastname' and entity_type_id = 1)
where lastname.value is null;

And it would really be great, if you vote on / accept answers for the questions you ask - that's the way how others can also take profit from what we are donig here. But you are anyway welcome :-)

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.