MySQL Cookbook, 2nd Edition

Using User-Defined Variables in SQL Statements

Problem

You want to save a value produced by an expression so that you can refer to it in a subsequent statement.

Solution

Use a user-defined variable to store the value for later use.

Discussion

You can assign a value returned by a SELECT statement to a user-defined variable, and then refer to the variable later in your mysql session. This provides a way to save a result returned from one statement, and then refer to it later in other statements. The syntax for assigning a value to a user variable within a SELECT statement is @ var_name := value, where var_name is the variable name, and value is a value that you’re retrieving. The variable can be used in subsequent statements wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

A common situation in which user variables come in handy is when you need to issue successive statements on multiple tables that are related by a common key value. Suppose that 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, and then delete rows 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, ...