Mysql using variables in select

Asked

Viewed 8,186 times

3

I already searched in some tutorials but I do not find a specification of how to declare the variable in select by Mysql, follows what I am trying to do.

DECLARE @idPedido INT;
DECLARE @idCombo INT;

SELECT @idPedido = idPedido, @idCombo = idCombo 
FROM Pedidos
WHERE idCombo IS NOT NULL;

SELECT @idPedido;
SELECT @idCombo;
mysql sql

  • You are doing the select and want to change the name of the variable that is returned, this ?

  • I want to create the variables and add the value of those fields inside them.

  • This is more complicated to do, if I were only to change the name I could use SELECT idPedido as novaVariavel ...

2 answers

3


If you wanted a predefined variable, use the SET:

-- seta o valor da variável
SET @idPedido = 10;

-- imprime o valor
SELECT @idPedido;  --neste caso, o retorno será 10

Using a query to search and set variables:

-- setando a variável (utiliza-se :=)
SELECT @idPedido := idPedido, @idCombo := idCombo 
FROM Pedidos
WHERE idCombo IS NOT NULL;

-- imprime o valor
SELECT @idPedido, @idCombo;

ATTENTION: in this example above, if the "SELECT" returns several results, and with that the variables will be set according to the last value returned by the query, as it is replaced as the query and update process runs.

2

You’re mixing it all up.

To make select do so: 

 SELECT idPedido, idCombo 
 FROM Pedidos
 WHERE idPedido = @idPedido AND idCombo = @idCombo

and to do update do so:

 UPDATE Pedidos set idPedido = @idPedido, idCombo = @idCombo 
 WHERE id = 1

To do select by assigning the values returned to the variable do so:

DECLARE @idPedido INT;
DECLARE @idCombo INT;

SELECT idPedido, idCombo into @idPedido, @idCombo
FROM Pedidos
WHERE idCombo = 1;

Attention SELECT must return 1 and only 1 line.

  • it was that no, it was not assigning the value in the variable.

  • thank you, that was it.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.