Help with SQL query

[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

[Tomik]

i dont know about the sql but you try the store manager trial and export/import

[zimmer-media]

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]

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]

I always do such things in a modules 'getcontent' method. Are you familiar with php?

[Lathaneo]

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.

[datakick]

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