Thread: Database bulk edit help

Hello, I've now got our createaccount.aspx modified so that all new users can only select residential as their address type. We were getting killed by customers selecting commercial to receive fedex ground rates instead of home delivery. I had tried to just filter Fedex Ground but when customers were using a free shipping coupon code that had created their account with a commercial address type, no free shipping methods were being returned. What I need to do now is find a way to change the shipping address type for all our existing customers in the database so the next time they re-order they won't run into the same problem. Can anyone share with me the SQL code I need to run to bulk edit this change?

I'm not sure if I follow. Do you just want a simple SQL statement that will update all your customers that have address type 'A' to now say address type 'B'?

Originally Posted by ZachJ85

I'm not sure if I follow. Do you just want a simple SQL statement that will update all your customers that have address type 'A' to now say address type 'B'?

Yes, that's exactly it. I have customers in the database shippingaddresstype currently mixed between residential, commercial, unknown. I'd like the SQL statement to set them all to residential. I already changed the create account page to set residential as the only option. What would be really useful is an appconfig that would allow us to forceallresidential=true.

Btw, is it possible to just remove the prompt and the drop down menu completely for new accounts and just have the system return residential rates in the background? I'm sorry if these are stupid questions, I'm a business owner not a programmer.

You can run an update query like below:

Code:

Update Customer set ResidenceType=1

You can place a whereclause in case you only want to update selected records.

Where:
Unknown = 0,
Residential = 1,
Commercial = 2

I just cut and paste that and hit execute, right? I'm more concerned about the shipping address type than I am the billing as that seems to determine the rates that get returned.

Yeah, that is cut and paste code that he posted. Just to be clear if you do run that, EVERYONE in the Customer table will now have ResidenceType=1. Is that ok?