Using SQL Variables in Queries
Problem
You want to save a value from a query so you can refer to it in a subsequent query.
Solution
Use a SQL variable to store the value for later use.
Discussion
As of MySQL 3.23.6, you can assign a value returned by a
SELECT statement to a variable, then refer to the
variable later in your mysql session. This
provides a way to save a result returned from one query, then refer
to it later in other queries. The syntax for assigning a value to a
SQL
variable within a SELECT query is
@var_name
:=
value, where
var_name is the variable name and
value is a value that
you’re retrieving. The variable may be used in
subsequent queries wherever an expression is allowed, such as in a
WHERE clause or in an INSERT
statement.
A common situation in which SQL variables come in handy is when you
need to issue successive queries on multiple tables that are related
by a common key value. Suppose you have a
customers table with a cust_id
column that identifies each customer, and an
orders table that also has a
cust_id column to indicate which customer each
order is associated with. If you have a customer name and you want to
delete the customer record as well as all the
customer’s orders, you need to determine the proper
cust_id value for that customer, then delete
records from both the customers and
orders tables that match the ID. One way to do
this is to first save the ID value in a variable, then refer to the
variable in the DELETE statements:[5]
mysql> SELECT @id ...