update table using if statement

Question

I want update price in table A using if statement, I have tried it but it can't be that I can only do calculations by calling id one by one, how to calculate the price directly? this is the table:

TABLE A

-------------------------
id | type | total | price
-------------------------
1  |  a1  |   80  | [  ]
2  |  a2  |   50  | [  ] 
3  |  a2  |   15  | [  ]

this is the if statement

        if($type=='a1')
        {
            $price1=1050;
            $price2=1500;
            $price3=2000;
            if($total <= 10)
            {
                $price = $total*$price1;
            }
            elseif($total <= 20)
            {
                $a = 10;
                $remtotal = $total-$a;
                $price = ($a*$price1)+($remtotal*$price2);
            }
            elseif($total > 20)
            {
                $a = 10;
                $b = 10;
                $remtotal = ($total-$a)-$b;
                $price = ($a*$price1)+($b*$price2)+($remtotal*$price3);
            }

        }
        elseif($type=='a2')
        {
            $price1=2100;
            $price2=3000;
            $price3=4000;
            if($total <= 10)
            {
                $price = $total*$price1;
            }
            elseif($total <= 20)
            {
                $a = 10;
                $remtotal = $total-$a;
                $price = ($a*$price1)+($remtotal*$price2);
            }
            elseif($total > 20)
            {
                $a = 10;
                $b = 10;
                $remtotal = ($total-$a)-$b;
                $price = ($a*$price1)+($b*$price2)+($remtotal*$price3);
            }
        }



SELECT id,type,total,price FROM TABLE A WHERE id='id'


UPDATE TABLE A SET price='$price' WHERE id=id"

I can count one by one by calling id but which I hope can immediately count all at once

expected result

-------------------------
id | type | total | price
-------------------------
1  |  a1  |   80  | [145500]
2  |  a2  |   50  | [171000] 
3  |  a2  |   15  | [28500]

How to do it?

Solved

I create 2 table

        TABLE A
-------------------------
id | type | total | price
-------------------------
1  |  a1  |   80  | [ ? ]
2  |  a2  |   50  | [ ? ] 
3  |  a2  |   15  | [ ? ]


              TABLE B
| type | price1 | price2 | price3 |
| ---- | ------ | ------ | ------ |
| a1   | 1050   | 1500   | 2000   |
| a2   | 2100   | 3000   | 4000   |
| a2   | 2100   | 3000   | 4000   |

this is the code

UPDATE tabelA ta, tabelB tb
SET ta.price = ( CASE 
       WHEN ta.type = 'a1' AND ta.total <= 10
       THEN ta.total * tb.price1
    
       WHEN ta.type = 'a1' AND ta.total <= 20
       THEN (10 * tb.price1) + ((ta.total - 10) * tb.price2)
       
       WHEN ta.type = 'a1' AND ta.total > 20
       THEN (10 * tb.price1) + (10 * tb.price2) + (((ta.total - 10) - 10) * tb.price3)
                
       WHEN ta.type = 'a2' AND ta.total <= 10
       THEN ta.total * tb.price1
    
       WHEN ta.type = 'a2' AND ta.total <= 20
       THEN (10 * tb.price1) + ((ta.total - 10) * tb.price2)
       
       WHEN ta.type = 'a2' AND ta.total > 20
       THEN (10 * tb.price1) + (10 * tb.price2) + (((ta.total - 10) - 10) * tb.price3)
     END )
WHERE ta.type = tb.type


        TABLE A
-------------------------
id | type | total | price
-------------------------
1  |  a1  |   80  | [145500]
2  |  a2  |   50  | [171000] 
3  |  a2  |   15  | [28500]

I want to upload the price of the if statement that I made into the table — Just bediL
– Just bediL, Commented Nov 28, 2018 at 11:12
Please move your solution to an answer of its own, thank you. — Cœur
– Cœur, Commented Dec 31, 2018 at 8:55
Please do not add the answer in your question. You should create an answer yourself and accept it, that's how it works here. — Thomas G
– Thomas G, Commented Dec 31, 2018 at 8:58

Raymond Nijland · Accepted Answer · 2018-11-28 11:30:20Z

The most easy thing is making a prices table.

Create table/insert

CREATE TABLE prices (
  `id` INTEGER,
  `type` VARCHAR(2),
  `price1` DOUBLE,
  `price2` DOUBLE,
  `price3` DOUBLE
);  

INSERT INTo prices (id, type, price1, price2, price3) VALUES(1, 'a1', 1050, 1500, 2000);

And INNER JOIN that with your real table and calculate within the CASE END clauses.

Query

SELECT 
 *
 , (
     CASE
       WHEN Table1.type = 'a1' AND Table1.total <= 10
       THEN Table1.total * prices.price1

       WHEN Table1.type = 'a1' AND Table1.total <= 20
       THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)

       WHEN Table1.type = 'a1' AND Table1.total > 20
       THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)       
     END
   ) AS price_total
FROM 
 Table1
INNER JOIN 
 prices 
ON
 Table1.type = prices.type

Result

| id  | type | total | id  | type | price1 | price2 | price3 | price_total |
| --- | ---- | ----- | --- | ---- | ------ | ------ | ------ | ----------- |
| 1   | a1   | 80    | 1   | a1   | 1050   | 1500   | 2000   | 145500      |

demo

sorry for that , I have add what I expected output

Edited

i just noticed the PHP calculation code for type a1 and a2 are the same so you are repeating yourself there.

The query should/can be without a type.

Query

SELECT 
 *
 , (
     CASE
       WHEN Table1.total <= 10
       THEN Table1.total * prices.price1

       WHEN Table1.total <= 20
       THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)

       WHEN Table1.total > 20
       THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)       
     END
   ) AS price_total
FROM 
 Table1
INNER JOIN 
 prices 
ON
 Table1.type = prices.type

Results

| id  | type | total | id  | type | price1 | price2 | price3 | price_total |
| --- | ---- | ----- | --- | ---- | ------ | ------ | ------ | ----------- |
| 1   | a1   | 80    | 1   | a1   | 1050   | 1500   | 2000   | 145500      |
| 2   | a2   | 50    | 1   | a2   | 2100   | 3000   | 4000   | 171000      |
| 3   | a2   | 15    | 1   | a2   | 2100   | 3000   | 4000   | 36000       |

demo

Otherwise you need to repeat it in the query like so.

Query

SELECT 
 *
 , (
     CASE
       WHEN Table1.type = 'a1' AND Table1.total <= 10
       THEN Table1.total * prices.price1

       WHEN Table1.type = 'a1' AND Table1.total <= 20
       THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)

       WHEN Table1.type = 'a1' AND Table1.total > 20
       THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)       


       WHEN Table1.type = 'a2' AND Table1.total <= 10
       THEN Table1.total * prices.price1

       WHEN Table1.type = 'a2' AND Table1.total <= 20
       THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)

       WHEN Table1.type = 'a2' AND Table1.total > 20
       THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)     
     END
   ) AS price_total
FROM 
 Table1
INNER JOIN 
 prices 
ON
 Table1.type = prices.type

I understand, what if I only want output id, type, total, pricetotal . By the way, thank you very much, I understand a lot now
then you need to replace Table.* with more column names like Table1.id, Table1.type and so on @JustbediL

Collectives™ on Stack Overflow

update table using if statement

Solved

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Solved

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related