CODEWITHSUNDEEP
mysql
marcq
marcq

Reputation: 483

Check field value, do a calculation (division) and update column values

I need to make some calculation in MySQL table which contains hundreds of rows.

I've got a field called "VAT-Code" which contain a code : 1 or 2

I've got a field called "Price" which contain the price

Query should do the following :

If "VAT-Code" value = 1 calculate "Price" / 1,08 -> update the field value

If "VAT-Code" value = 2 calculate "Price" / 1,025 -> update the field value

Ok I have tried this :

I have tried this :

UPDATE jos_tempcsv SET selling price = CASE `VAT-Code` WHEN = 1 THEN `selling price`/1.08 WHEN = 2 THEN `selling price`/1.025 ELSE NULL END

selling price is the field with the price

But I get the following error :

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 'price = CASE VAT-Code WHEN = 1 THEN selling price/1.08 WHEN = 2 THEN `sellin' at line 1

Upvotes: 0

Views: 569

Answers (3)

marcq
marcq

Reputation: 483

This query works fine now :

UPDATE jos_tempcsv SET `selling price` = `selling price`/1.08 WHERE `VAT-Code`=1;

UPDATE jos_tempcsv SET `selling price` = `selling price`/1.025 WHERE `VAT-Code`=2;

Two queries in one

UPDATE jos_tempcsv SET `selling price` = CASE `VAT-Code` WHEN 1 THEN `selling price`/1.08 WHEN 2 THEN `selling price`/1.025 ELSE NULL END;

Upvotes: 0

Happy Coding
Happy Coding

Reputation: 2525

You can try the below sql query :

UPDATE yourtable table1,(SELECT * FROM `yourtable` WHERE VAT-Code = 1) table2 SET table1.yourfield = (table2.price/1.08) WHERE table1.VAT-Code = 1

UPDATE yourtable table1,(SELECT * FROM `yourtable` WHERE VAT-Code = 2) table2 SET table1.yourfield = (table2.price/1.025) WHERE table1.VAT-Code = 1

Upvotes: 0

vhu
vhu

Reputation: 12798

You can run UPDATE with CASE:

UPDATE yourtable SET yourfield=CASE `VAT-Code` WHEN 1 THEN `Price`/1.08 WHEN 2 THEN `Price`/1.025 ELSE NULL END;

Or you can run this as two separate UPDATEs:

UPDATE yourtable SET yourfield=`Price`/1.08 WHERE `VAT-Code`=1;
UPDATE yourtable SET yourfield=`Price`/1.025 WHERE `VAT-Code`=2;

Upvotes: 2

Related Questions