Reputation: 3
I have 2 tables.
table1
CATEGORY | RANGE | PERCENTAGE
REG | 250001 | 50
REG | 500001 | 40
REG | 9999999 | 30
VIP | 5001 | 50
VIP | 10001 | 40
VIP | 9999999 | 30
table 2
CATEGORY | PRICE | DISCOUNT
REG | 100000 | 0
REG | 400000 | 0
REG | 600000 | 0
VIP | 3000 | 0
VIP | 6000 | 0
VIP | 120000 | 0
I would like to update DISCOUNT
in table 2
based on RANGE
and PERCENTAGE
from table 1
CATEGORY REG
WHEN PRICE is <= 250001
DISCOUNT = PRICE * 50%
WHEN PRICE is > 250001 AND <= 500001
DISCOUNT = PRICE * 40%
WHEN PRICE is > 500001
DISCOUNT = PRICE * 30%
CATEGORY VIP
WHEN PRICE is <= 5001
DISCOUNT = PRICE * 50%
WHEN PRICE is > 10001 AND <= 500001
DISCOUNT = PRICE * 40%
WHEN PRICE is > 100001
DISCOUNT = PRICE * 30%
Upvotes: 0
Views: 1459
Reputation: 160
Try below query for category 'REG'. You can change where category and condition in WHEN clause according your requirement.
UPDATE table2
SET discount = CASE
WHEN price <= 250001 THEN ((price*50)/100)
WHEN (price BETWEEN 250002 AND 500001) THEN ((price*40)/100)
WHEN price > 500001 THEN ((price*30)/100)
ELSE discount
END
WHERE category = 'REG'
Check updated query for category 'REG':
UPDATE table2 SET discount =
(SELECT ((price*a.percentage)/100) FROM table1 as a WHERE price <= a.range AND category='REG' LIMIT 1)
WHERE category = 'REG'
Check with below query (Edit#2):
UPDATE table2 AS b SET discount =
(SELECT ((b.price*a.percentage)/100) FROM table1 as a WHERE a.range >= b.price AND a.category=b.category LIMIT 1)
Make sure range column is quoted because range is a MYSQL reserved keyword. Also note that datatype of range column should be integer.
Upvotes: 0
Reputation: 4393
Try this:
UPDATE table2 t2 SET
DISCOUNT = (
SELECT max(t1.`percentage`) * t2.price / 100 FROM table1 t1
WHERE
t1.category = t2.category AND
t2.price < t1.`range`
)
Upvotes: 1