CODEWITHSUNDEEP
mysqlsql-updatecase
Sanju Menon
Sanju Menon

Reputation: 729

Update column in one table based on value in another table in mysql

I have a table called 'quot_items', that holds a grand_total field . It has this structure

tender_id |  grand_total
15001          100000
15002          250000
15003          1500000
16009          4500000

I have another table called 'quotation' which holds a 'category' field. It has this structure.

tender_id |  category
15001          H
15002          E
15003          B
16009          A

What iam trying is i need an UPDATE statement in MYSQL where in there are certain conditions:

If [grand_total'] < '100000') then Category H ( which means if grand_total value of table 'quot_items' is < 100000 then Update table 'quotation' category field as 'H'.

If (['grand_total'] >= '100000') && (['grand_total'] <= '200000') then Category 'G'.

If (['grand_total'] > '200000') && (['grand_total'] <= '600000') then Category 'F'.

If (['grand_total'] > '600000') && (['grand_total'] <= '1000000') then Category 'E'.

If (['grand_total'] > '1000000') && (['grand_total'] <= '1500000') then Category 'D'.

There are more conditions. I need a query to do this in MYSQL so that i can UPDATE my DB in one update statement. Pls anyone can help me.

I have tried the following:

UPDATE quotation INNER JOIN quotation_items ON quotation.tender_id = quotation_items.tender_id
SET quotation.category = (
    CASE WHEN quotation_items.grand_total < 100000 then 'H' 
    WHEN quotation_items.grand_total >= 100000 && quotation_items.grand_total <= 200000 then 'G'
    WHEN quotation_items.grand_total > 200000 && quotation_items.grand_total <= 600000 then 'F'
    WHEN quotation_items.grand_total > 600000 && quotation_items.grand_total <= 1000000 then 'E'
    WHEN quotation_items.grand_total > 1000000 && quotation_items.grand_total <= 1500000 then 'D'
    END
);

Upvotes: 1

Views: 28

Answers (2)

Blank
Blank

Reputation: 12378

Try this:

UPDATE quotation t1
INNER JOIN quot_items t2
ON t1.tender_id = t2.tender_id
SET t1.category = 
    CASE WHEN t2.grand_total < 100000 THEN 'H' 
    WHEN grand_total >= 100000 AND grand_total <= 200000 THEN 'G'
    WHEN grand_total > 200000 AND grand_total <= 600000 THEN 'F'
    WHEN grand_total > 600000 AND grand_total <= 1000000 THEN 'E'
    WHEN grand_total > 1000000 AND grand_total <= 1500000 THEN 'D'
    ELSE t1.category
    END

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use CASE expression.

Query

SELECT tender_id,
CASE WHEN grand_total < 100000 then 'H' 
WHEN grand_total >= 100000 && grand_total <= 200000 then 'G'
WHEN grand_total > 200000 && grand_total <= 600000 then 'F'
WHEN grand_total > 600000 && grand_total <= 1000000 then 'E'
WHEN grand_total > 1000000 && grand_total <= 1500000 then 'D'
END AS Category
FROM quot_items;

And if you want to update the category column. Then,

UPDATE quot_items
SET category = (
    CASE WHEN grand_total < 100000 then 'H' 
    WHEN grand_total >= 100000 && grand_total <= 200000 then 'G'
    WHEN grand_total > 200000 && grand_total <= 600000 then 'F'
    WHEN grand_total > 600000 && grand_total <= 1000000 then 'E'
    WHEN grand_total > 1000000 && grand_total <= 1500000 then 'D'
    END
);

Upvotes: 1

Related Questions