SQL export/import query question

[daniel]

I just migrated a very very old version of Joomla/Virtuemart to the latest stable releases of both.
The old site/vrituemart used a modified database (the products table contained two extra fields). These two fields are not the only ones I need to migrate (I already added them to the database).

How do I, in phpmyadmin/mysql, do the following:
1 – export out of the old database for all records the unique identifier + the two customly added fields.
2 – upload into the new database, for all records, for each unique identifier, the two custom fields into the corresponding fields which are at the moment empty.

In other words how do the following sql queries look

OLD DATABASES:

Code Select Expand

EXPORT OUT OF TABLE jos_vm_product FOR EVERY product_sku
THE FIELDS product_sku, custom_field1, custom_field2
INTO text file

NEW DATABASE

Code Select Expand

INSERT INTO TABLE new_vm_product FOR EVERY product_sku
THE FIELDS custom_field1, custom_field2

[John2400]

http://www.csvimproved.com/
might help

[daniel]

@John2400: Thanks, i Know that extension. It works pretty well. But because of the huge amount of products (5000+) I'd rather finisch migration by exporting and importing directly in database.

[bytelord]

Hello,

Why you are not using phpmyadmin to export and import the data, will process the queries as you wish ...

Regards

[daniel]

@bytelord: thank you. However, in PHPMyAdmin how do I export just a couple of fields from a table. If i export the table as export type "INSERT". It exports the whole table jos_vm_product

It gives me this sql code:

Code Select Expand

INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`, `custom_field1`, `custom_field2`) VALUES

and then the values to all these fields for 5000+ products.

How do I export just the unique identifier (say: product_sku) and the fields custom_field1 and custom_field2 as an INSERT? Or export it and write the correct SQL statement around is?

Or can ayone confirm that the table structure between VM1 and VM2 has not changed and I can just INSERT the whole product table?

[bytelord]

Hello Daniel,

I can't help you with VM1 because i have never use it. As john suggests you could use csv improved, i don't think that 5000 product is too huge that csvi cannot handle it ...

Regards

[daniel]

Thanks for all the answers. But it is not a VM question, but a SQL question I think.

How do I write an SQL statement to update field x, and Y in RECORD where productID=a

CSV Improved does not allow me to export customly added database fields from old directory and import customly added database fields into new database

[bytelord]

UPDATE 'table_name' SET field_x = '[new_content]',  SET field_y = '[new_content]' WHERE productID = 'product_id';

or

UPDATE 'table_name'
SET field_x = 'new_content',  field_y = 'new_content'
WHERE
productID = 'product_id';

* [1] new_content quotes ' ' depends on the data
* [2] select the desire table first

Regards

[daniel]

Thanks
By exporting the product table out of the old database, importing that into Excel as CSV, dropping some columns, adding SQL statements and combining cells i came to the following 5000+ lines of SQL:

Code Select Expand

UPDATE mmm_virtuemart_products SET custfieldA='lokA', custfieldB='1111' WHERE product_sku='test'
UPDATE mmm_virtuemart_products SET custfieldA='lokB', custfieldB='2222' WHERE product_sku='hanst20'
UPDATE mmm_virtuemart_products SET custfieldA='GI0', custfieldB='0,00' WHERE product_sku='905035-62'
UPDATE mmm_virtuemart_products SET custfieldA='T47', custfieldB='0,00' WHERE product_sku='print fettle pre'

How do i combine the above to one SQL statement? inserting the above in PHPMyAdmin gives an error. I can however insert it line by line which goes through correctly. But with 5000+ lines that is too much 

[bytelord]

You could add a transaction on that, also try with semicolon in each line

START TRANSACTION;
UPDATE mmm_virtuemart_products SET custfieldA='lokA', custfieldB='1111' WHERE product_sku='test';
UPDATE mmm_virtuemart_products SET custfieldA='lokB', custfieldB='2222' WHERE product_sku='hanst20';
UPDATE mmm_virtuemart_products SET custfieldA='GI0', custfieldB='0,00' WHERE product_sku='905035-62';
UPDATE mmm_virtuemart_products SET custfieldA='T47', custfieldB='0,00' WHERE product_sku='print fettle pre';
COMMIT;

[daniel]

Like a charm! thanks a lot!!!!

Case closed ;-)

[bytelord]

Great