SQL mass update prices - solved

[Golemtech]

Hi. Are you able to help me with SQL query?

In table ps_product_shop, I have on row1 id_shop = 1 and ond row2 id_shop=2

I need to update my prices for the different shops.

Take "price" from id_shop=1, divide it 25.5 (CZK / EUR) and set it to price for id_shop=2

I was several years before Oracle db administrator, but I am out of ths bussiness now... 

Thank you. Komanche

Edited August 9, 2018 by Golemtech
solved (see edit history)

[Golemtech]

update ps_product_shop set price.2=price.1/25.5 where id_product = *the_same* 

price.1 -> id_shop=1

price.2 -> id_shop=2

id_product -> the same for both shops

 

this is the schema...  how to add it together?

[Golemtech]

will this work?

update ps_product_shop ps2 join
       ps_product_shop ps1
       on ps.id_product = ps2.id_product and
          ps1.id_shop = 1 and ps2.id_shop = 2
    set ps2.price = ps1.price / 25.5;

 

[Golemtech]

solved:

update ps_product_shop pps1 join ps_product_shop pps2
on pps1.id_product = pps2.id_product
set pps2.price = pps1.price/25.5 where pps2.id_shop='2';