update specific data in database after PS upgrade

[Frevab]

I have updated my webshop from 1.4.9 to 1.7.2 but want to update some data in the database to correct some issues between the 2 versions:

I want to add a add a phone number to the customers who did not enter a phone number. After the upgrade the phone number was not a obligated filed as in the old version it was. I have changed it to obligated but now accounts that were made in the meantime cannot order anymore. What SQL command to I have to use to get the fields that are empty filled with a set of numbers?

In the old version I used a shipped module which was different from the way shipping costs and carriers are handled in the new version. I need to add a weight to products to avoid them being ordered with nil shipping cost. I want to add a fixed weight of 1 kg to those products where the weight is blank or 0, leaving those products unchanged that already have a weight. What SQL commands to I use?

Who can help me with this?

[bellini13]

phone numbers are stored with addresses, so look in the ps_address table

[Frevab]

On 22-6-2018 at 1:52 PM, bellini13 said:

phone numbers are stored with addresses, so look in the ps_address table

 

Yes I know, In my caae I have 2 files in ps_address: "phone"and "phone_mobile" as this is a result from previous version 1.4.9

So in this case

if "phone"is empty

and

if "phone_mobile" is not empty

then copy "phone_mobile" to "phone"

and then after this command has been exectuted

if "phone"is empty

fill with "999-9999999" no make sure there are no addresses with empty "phone" field.

how does this translate into the correct SQL-query ?

 

same I want to do with ps_product

if field "weight" is empty fill it with i.e. "1,000"

to avoid products without any weight filled in.

 

Can you help me with the correct SQL-query

[bellini13]

On 6/26/2018 at 5:53 PM, Frevab said:

if "phone"is empty

and

if "phone_mobile" is not empty

then copy "phone_mobile" to "phone"

This should cover the above

UPDATE ps_address
SET phone = phone_mobile
WHERE phone IS NULL and phone_mobile IS NOT NULL

 

On 6/26/2018 at 5:53 PM, Frevab said:

if "phone"is empty

fill with "999-9999999" no make sure there are no addresses with empty "phone" field.

and this should cover the above

UPDATE ps_address
SET phone = '999-9999999'
WHERE phone IS NULL

 

[marczoo]

Many thanks for the solution, this problem drove me crazy.

I used the MigrationPro module to migrate from PS 1.5 to PS 1.7 and for some reason the module doesn't correct that problem with telephone numbers. The result was that the previously registered customers that only entered a mobile phone, was stuck at delivery address step of the checkout process.

[MigrationPro]

Hello Marczoo,
 

I want to you know that using our module you will migrate your data from one version to another one and our product migrates your current shop data to the new one. The module doesn't provide any correction. if you have another problem please contact us from the sale page.

Thank you for understanding