Troy.Roberts Posted January 6, 2018 Share Posted January 6, 2018 Hi i need some help with a sql query. I want to edit the retail price of products in the psproductshop 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 Link to comment Share on other sites More sharing options...
0 Tomik Posted January 6, 2018 Share Posted January 6, 2018 i dont know about the sql but you try the store manager trial and export/import Link to comment Share on other sites More sharing options...
0 zimmer-media Posted January 6, 2018 Share Posted January 6, 2018 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. Link to comment Share on other sites More sharing options...
0 Troy.Roberts Posted January 7, 2018 Author Share Posted January 7, 2018 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 Link to comment Share on other sites More sharing options...
0 wakabayashi Posted January 7, 2018 Share Posted January 7, 2018 I always do such things in a modules 'getcontent' method. Are you familiar with php? Link to comment Share on other sites More sharing options...
0 Lathaneo Posted January 7, 2018 Share Posted January 7, 2018 Hi, 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; Regards. Link to comment Share on other sites More sharing options...
0 datakick Posted January 7, 2018 Share Posted January 7, 2018 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 Link to comment Share on other sites More sharing options...
Question
Troy.Roberts
Hi i need some help with a sql query. I want to edit the retail price of products in the psproductshop 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
Link to comment
Share on other sites
6 answers to this question
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now