Problems with query change price in 2 tables

I have a problem to change prices from 1 table to another table.

table 1
inventory
-itemnumber
-price

table 2
zen_products
-products_model
-products_price

in table 1 (inventory) are the right prices

it's has to compare the itemnumber with the products_model when it is the same, change the price from table zen_products with the price from tabel inventory.

I have try this for someny hours, but everytime with this query below the prices from table 2 = zero

below the query i use.

I hope somebody can help me with that.

Thanks.

UPDATE zen_products  
SET products_price =  ( SELECT inventory.Price
FROM inventory
WHERE inventory.itemNumber = zen_products.products_model)
WHERE EXISTS
  ( SELECT inventory.Price
    FROM inventory
    WHERE inventory.itemNumber = zen_products.products_model);

Why not make it a lot simpler, like this: ```
update zen_products p, inventory i
set p.products_price = i.Price
where p.products_model = i.itemNumber;

Thank you Dr. Byte for your fast reply

I don't know what is p and i?

Those are aliases which make it possible to avoid using tablenames repeatedly. p refers to zen_products and i refers to inventory

ok so i can copy exactly your query into my database
and that will be the solution?

I hope so :)

sorry, it's not working

0 rows bijgewerkt. ( query duurde 0.3136 sec )

UPDATE zen_products p,
inventory i SET p.products_price = i.Price WHERE p.products_model = i.itemNumber

This is a print from the 2 databases

in tabel inventory are the right prices
in zen_products are the old prices

Your posts used upper/lower case, but your tables use only lowercase in the field names.

So rewrite the query as:

update zen_products p, inventory i set p.products_price = i.price where p.products_model = i.itemnumber;

And you must run this query in phpMyAdmin. YOU CANNOT run it in your Zen Cart Admin sqlpatch tool.

thank you again dr. Byte, i had already see the mistake and changed that, but still not working

i do run this in phpMyAdmin

i have tryed somany solution the last 2 days, i am really frustrated

i don't know why it is not working

I tested it here before I posted my first solution for you, and it works fine for me.
I can in phpMyAdmin that my products_price field is correctly updated with the new prices for me.
And then in my store if I run Admin->tools->store manager->reset products price sorter, my storefront will also show the new prices correctly.

So something must be wrong with your data. Perhaps your itemnumber values don't properly match your products_model numbers. Mismatched itemnumber-vs-products_model would cause a 0 price to be entered.

When i start de query in Phpmyadmin, there is a message
i translate this message for you.

"there is missing a value in this form'

When did that message start? Why didn't you mention it earlier?

I just reconstructed a new table named inventory, added an itemnumber field and price field, using the same structure as the products_model and products_price fields in my zen_products table. Then I filled the inventory table with some sample entries, using itemnumbers that match products_model values.
I used phpMyAdmin to run the same query I posted above, and it worked without any error.

because the second time i started the query in the startscreen (localhost » jvw_testdb) , i don't know how to tell you.

the first try with the query i was in the table inventory ( localhost » jvw_testdb » inventory) and after that i push the SQL button,
and start the query, there was no message and no changes in the prices

the last try with the message i was here in ( localhost » jvw_testdb) and started the same query, with result that message.

i have found the problem why it is not working,

the field products_model in zen_products align to the left
the field itemnumber in inventory is floating?

How i don't know?

But now, how can i change the field itemnumber in inventory, also the the left?

thank you!

Hello Dr. Byte, it's just like you said, it's working.

I don't no what's happened that at beginning of the field 'itemnumber' there were al lot of spaces.
I have removed the spaces and use this query from you:

update zen_products p, inventory i set p.products_price = i.price where p.products_model = i.itemnumber;

in 0,5 seconds more then 33000 prices were changed!. Is that not GREAT news:smile:

Thank you to point me at "So something must be wrong with your data. Perhaps your itemnumber values don't properly match your products_model numbers. Mismatched itemnumber-vs-products_model would cause a 0 price to be entered"

End yes, the data was mismatched.

Again Dr. Byte, thank you so much for your time and help!!

:) Glad you got it sorted out.