CODEWITHSUNDEEP
mysql
ZoHaN
ZoHaN

Reputation: 3

How to UPDATE COLUMN with a condition FROM another table in SQL?

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

Answers (2)

Hardiksinh Gohil
Hardiksinh Gohil

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

Wajih
Wajih

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

Related Questions