Agathon Marinopoulos Agathon Marinopoulos - 3 months ago 42
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 2.1.0.1 )

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

Table we have to update price is

oc_product_special


and column we have to update is

oc_product_special.price


Of course we need products which cost 25€ from table

oc_product


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€

Error
#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
oc_product
INNER JOIN
oc_product_special
ON
oc_product
.product_id =' at line 2


Any help is appreciated
enter image description here

** EDIT

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

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
Comments