Help with SQL query
Troy.Roberts last edited by
Hi i need some help with a sql query. I want to edit the retail price of products in the ps_product_shop table with an id >9900001
I want retail price to be equal to (wholesale price * 2.86)
I can’t do it with a CSV import because of some sql triggers that go off that mess up the retail price.
So my idea is to just run a sql query to update the table directly.
hope someone’s SQL fu is better than mine
Tomik last edited by
i dont know about the sql but you try the store manager trial and export/import
zimmer-media last edited by
I think, if you use the great tool (script) of @musicmaster, you can eg with preselection (filter) export the product list. then rework it in your spreadsheet program such as Excel or OpenOffice and then simply import the necessary data. If you are not used to it, with a little practice in my view it is absolutely no problem.
Troy.Roberts last edited by
Sorry those method’s won’t work due to the SQL triggers. They’re designed to apply the markup once the database itself is updated upon any changes to a row in the database.
It’s quite handy however now it’s biting me in the butt now that i want to add some products that don’t use the same markup scheme
wakabayashi last edited by
I always do such things in a modules ‘getcontent’ method. Are you familiar with php?
Lathaneo last edited by Lathaneo
Just replace right variables with what you want in :
DROP PROCEDURE IF EXISTS CHANGETHERETAILPRICE; delimiter $$ CREATE PROCEDURE CHANGETHERETAILPRICE() BEGIN DECLARE n INT DEFAULT 0; DECLARE i INT DEFAULT 0; DECLARE coef FLOAT DEFAULT 0; SELECT count(*) as count FROM tb_product_shop INTO n; SET i=9900001; SET coef=2.86; WHILE i<=n DO UPDATE `tb_product_shop` SET `price`= (`tb_product_shop`.`wholesale_price` * coef) WHERE `tb_product_shop`.`id_product`= i AND `tb_product_shop`.`id_shop` = 1; SET i = i + 1; END WHILE; End; $$ CALL CHANGETHERETAILPRICE();
If don’t works, just tell me what’s in SELECT @@sql_mode;
datakick last edited by
it’s not possible to disable mysql sql triggers. The only thing you can do is drop the trigger, perform update, and then recreate the trigger.
Or maybe your trigger contains some conditional statement that checks global / session variable before it performs its logic. If the variable is set, the trigger does nothing. Something like this:
IF @disable_triggers IS NULL THEN // Do something use as the trigger isn't disabled. END IF;
if that’s the case, you can:
SET @disable_triggers = 1; UPDATE tb_product_shop SET price=wholesale_price*2.86 WHERE id_product > 9900001; SET @disable_triggers = NULL;
If you trigger does not contain this, you really have go with drop trigger / update / create trigger route. In this case, I recommend to wrap the trigger body into conditional statement above