Function MYSQL - IF

Question

Function MYSQL - IF

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

Create a function that modifies the sale price of a product based on the quantity in stock: a) If the amount is 300, the price increase will be of a value of 5 * /

I am trying to do this exercise and it does not work out in any way. I leave as I've been thinking, although the code that I attached gives you syntax errors.

drop function if exists funcion2; 
set global  log_bin_trust_function_creators = 1 ;

DELIMITER //
create function funcion2(cod_producto int(5))
returns int(5)

begin

declare PrecioVenta int(5);

select PrecioVenta
from productos
where CodigoProducto.productos=cod_producto;

if PrecioVenta<100 then update productos set PrecioVenta=PrecioVenta+15;
elseif (PrecioVenta>100 and PrecioVenta<300) then update productos set PrecioVenta=PrecioVenta+10;
else update productos set PrecioVenta=PrecioVenta+5;

end//

DELIMITER ;

The product table and a new attempt with what you have recommended me, but nothing ...

drop function if exists funcion2; 
set global  log_bin_trust_function_creators = 1 ;

DELIMITER //
create function funcion2(cod_producto int(5))
returns int(5)

begin

declare PrecioVenta int(5);

select CantidadEnStock, PrecioVenta
from productos
where productos.CodigoProducto=cod_producto;

UPDATE productos 
    SET PrecioVenta = CASE
                         WHEN CantidadEnStock < 100 THEN PrecioVenta + 15
                         WHEN CantidadEnStock >= 100 AND CantidadEnStock <= 300 THEN PrecioVenta + 10
                         WHEN CantidadEnStock > 300 THEN PrecioVenta + 5
    END
    WHERE productos.CodigoProducto = cod_producto;

return PrecioVenta;

end//

DELIMITER ;

mysql if

asked by Alvaro Montoro 27.12.2017 в 00:39

source

2 answers

Add a column several times with different conditions Parameters between URL's

score 2 · Answer 1

Your code has some problems, let's see:

The function receives a parameter that is a product code, that data is used in a SELECT , initially to obtain a PrecioVenta but then you are not using it in the statement UPDATE , therefore you would end up updating all the products not only the one with which you invoke the function.
The SELECT has a problem in the WHERE , this is not valid: where CodigoProducto.productos=cod_producto , the use of the alias should be alias_de_tabla.columna ie where productos.CodigoProducto=cod_producto
The IF statements such as if PrecioVenta<100 do not match the statement, it should be cantidad , which you should have read previously in SELECT .

You could correct these topics and keep more or less your initial idea for the function or you can simplify everything in a single statement of UPDATE that you configure by using the CASE statement:

UPDATE productos 
    SET PrecioVenta = CASE
                         WHEN cantidad < 100 THEN PrecioVenta + 15
                         WHEN cantidad >= 100 AND cantidad <= 300 THEN PrecioVenta + 10
                         WHEN cantidad > 300 THEN PrecioVenta + 5
    END
    WHERE CodigoProducto = cod_producto

score 0 · Answer 2

I have no idea of the structure of your tables, so here is a generic solution.

UPDATE productos SET PrecioVenta= PrecioVenta+ 15 where cantidad < 100;
UPDATE productos SET PrecioVenta= PrecioVenta+ 10 where cantidad >= 100 and cantidad < 300;
UPDATE productos SET PrecioVenta= PrecioVenta+ 5 where cantidad >= 300;

If you put this in your procedure, it will update the values as you need them