Need a little bit MySQL help


EvilDragon

Administrator
Staff member
Joined
Mar 4, 2003
Messages
29,986
Age
46
Location
Ingolstadt
I'm pretty sure it's not too hard and there are some here who can help me.

I need a little SQL query where I can export the results to import them into a different database.

As you might've seen, I've upgraded my shop and have everything imported except for the mail-alert table (for products when they're back on stock).

The old PrestaShop didn't have that, so I used a module, but the new version can do that out of the box, but the two tables are a tiny bit different.

These are the relevant fields of the new table (called ps_mailalert_customer_oos)

id_customer

customer_email

id_product

id_product_attribute

id_lang

These are the revelant fields of the old table (called ps_belvg_preorder_wait)

id_product

id_product_attribute

id_customer

id_lang

As you can see, there is only ONE field missing - that's the customer_email field.

All other fields are exactly the same.

So what I need is a query that exports these fields from ps_belvg_preorder_wait but adds the customer_email field.

The eMail address can be grabbed from another table called ps_customer

That table has id_customer as field, and email as another field (which has the eMail address in there).

The result should be something I can easily export (either as SQL file, as CSV, etc.) so I can import it into the new database.

Again, a quick summary: export the following from ps_belvg_preoder_wait

id_customer (available)

customer_email (can be gotten from table ps_customer, field email using id_customer as row)

id_product (available)

id_product_attribute (available)

id_lang (available)

I've got phpmyadmin available, so can do some stuff there.

Can some help me create a useful query for that?

Thanks :)
 
Gosh, I chose a simpler means of setting up my website in the end. My sql would have been FAR too complicated for me, especially if you need help with it.

Not a techie guy, much, so im of no help at all.
 
Code:
insert into ps_mailalert_customer_oos (id_customer, customer_email, id_product, id_product_attribute, id_lang) 
  select w.id_customer, x.customer_email, w.id_product, w.id_product_attribute, w.id_lang
    from ps_belvg_preorder_wait w, ps_customer x
   where x.id_customer = w.id_customer;
commit;
Before doing so, better just run the select to verify the data (and maybe even a select count(*) from ps_belvg_preorder_wait w, ps_customer x where x.id_customer = w.id_customer; to check cardinality)
 
Thanks guys! I can try that without major loss, as I've got a backup database where I can  play around.

I'll run it there and then export / import the table when it worked :)
 
Back
Top