Agathon Marinopoulos Agathon Marinopoulos - 1 year ago 147
SQL Question

Opencart - phpMyAdmin Bulk update Special Price

I work as administrator in one electronic shop we are using OpenCart eCommerce and recently they asked me to add special price to Bulk products and leave it running for some days. Of course i can change it in admin page one by one. The things is how i can change it through phpMyAdmin.

( Opencart Version )

I have 46 products which cost 25€ and i have to add special price to 20€.

Table we have to update price is


and column we have to update is


Of course we need products which cost 25€ from table


to retrieve products from this table and add special price to 20€

The thing is how to connect these two tables so i can add special price which sql query to run so i can achieve this.

oc_product & oc_product_special , update this column oc_product_special.price

I 've tried to run this sql query but i receive error

UPDATE `oc_product_special`.price
SET `oc_product_special`.price = 16.1290
FROM `oc_product`
INNER JOIN `oc_product_special`
ON `oc_product`.product_id =`oc_product_special`.product_id
WHERE`oc_product`.price = 20.1612;

**** Price is without taxes 25€ / 1,24(tax rate) = 20.1612€

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM
.product_id =' at line 2

Any help is appreciated
enter image description here


Updating my Sql query

UPDATE oc_product_special AS ps
INNER JOIN oc_product op ON ps.product_id = op.product_id
set ps.price = 16.1290
where op.price = 20.1612

but the thing is that 0 rows affected. (Query took 0.0003 sec) , if i run select query with these prices i have results.

select *
from oc_product as op
where op.price=20.1612

Showing rows 0 - 29 (46 total, Query took 0.0010 sec)

Answer Source

Your query has no formatting and does not correspond to MySQL standards, you have to pay attention to the following:

  • After an UPDATE statement one should specify the table to use and not the column
  • The SET statement only comes after an INNER JOIN
  • Use suffixes for convenience when joining tables

Correct MySQL query below (change the prices):

UPDATE oc_product_special AS ps 
INNER JOIN oc_product op ON ps.product_id = op.product_id 
set ps.price = 22 where op.price = 0.00