Add with SQL conditions

Navigation
3

I am learning SQL and I have a database with two empty columns, Suma , Producto and I want to add the attributes of the selected names (I put a * so that they know which one I select) and I want to put a condition to my sum according to its size and the result of the condition I want to put it in the product column but I do not know how to do this. 

INSERT INTO Atleticos.Producto (Suma) VALUES (SUMA)

SELECT SUM(Attributos) Suma FROM Atleticos WHERE name = '????*'

IF Suma < 0.6
Producto = Suma*4 
ELSEIF Suma >= 0.6 and Suma<= 5
Producto = Suma * 3 + 5 
ELSE
Producto = Suma * 2;
ENDIF
  

Sum < 0.6, Multiply it by 4 and store it in Product

     

0.6 ≤ Sum ≤ 5, Multiply it by 3 + 5 and store it in Product

     

Sum > 5, Multiply it by 2 and store it in Product

This is the table, sum and product is what I want to fill out 

Nombres Attributos  Altura  Suma    Producto
Pedro*    0.5         5.1    0.5    2
Anna      0.6         4.3       
Carla*    0.3         6.4    0.8    7.4
Johan*    0.4         3.4    1.2    8.6
Bryan     10           6        
Cecilia*   5          5.7    6.2    12.4
    
asked by Jose Vasquez 08.07.2018 в 03:35
source

2 answers

1

your query should go as follows: 

UPDATE Atleticos
SET Producto = CASE
    when Suma < 0.6 THEN Suma*4
    when Suma >= 0.6 AND Suma<= 5 THEN Suma * 3 + 5
    ELSE Suma * 2
END
WHERE name = '????*'
    
answered by 08.07.2018 / 04:05
source
0

Consider doing it with a trigger (the syntax may change depending on your database manager) if you really want the data to be in the table, but that will occupy space unnecessarily. The best thing in those cases is to create a view so that the data is calculated when executing the query. Example: 

CREATE VIEW view_atleticos as SELECT *, CASE
when Suma < 0.6 THEN Suma*4
when Suma >= 0.6 AND Suma<= 5 THEN Suma * 3 + 5
ELSE Suma * 2
END Suma FROM Atleticos

And use your sight in your selects.

    
answered by 08.07.2018 в 16:06
Convert an array of objects to JSON Error adding the array with double data