### 12.4.4 Assignment Operators

[]()[]()

[]()

**Table 12.6 Assignment Operators**

|                         Name                          |                                                                Description                                                                |
|-------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------|
|[`:=`](assignment-operators.html#operator_assign-value)|                                                               Assign a value                                                              |
|[`=`](assignment-operators.html#operator_assign-equal) | Assign a value (as part of a [`SET`](set-variable.html) statement, or as part of the `SET` clause in an [`UPDATE`](update.html) statement)|

* []()

  [`:=`](assignment-operators.html#operator_assign-value)

  []()[]()

   Assignment operator. Causes the user variable on the left hand side of the operator to take on the value to its right. The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same [`SET`](set-variable.html) statement. You can perform multiple assignments in the same statement.

   Unlike [`=`](assignment-operators.html#operator_assign-equal), the [`:=`](assignment-operators.html#operator_assign-value) operator is never interpreted as a comparison operator. This means you can use [`:=`](assignment-operators.html#operator_assign-value) in any valid SQL statement (not just in [`SET`](set-variable.html) statements) to assign a value to a variable.

  ```
  mysql> SELECT @var1, @var2;
          -> NULL, NULL
  mysql> SELECT @var1 := 1, @var2;
          -> 1, NULL
  mysql> SELECT @var1, @var2;
          -> 1, NULL
  mysql> SELECT @var1, @var2 := @var1;
          -> 1, 1
  mysql> SELECT @var1, @var2;
          -> 1, 1

  mysql> SELECT @var1:=COUNT(*) FROM t1;
          -> 4
  mysql> SELECT @var1;
          -> 4
  ```

   You can make value assignments using [`:=`](assignment-operators.html#operator_assign-value) in other statements besides [`SELECT`](select.html), such as [`UPDATE`](update.html), as shown here:

  ```
  mysql> SELECT @var1;
          -> 4
  mysql> SELECT * FROM t1;
          -> 1, 3, 5, 7

  mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0

  mysql> SELECT @var1;
          -> 1
  mysql> SELECT * FROM t1;
          -> 2, 3, 5, 7
  ```

   While it is also possible both to set and to read the value of the same variable in a single SQL statement using the [`:=`](assignment-operators.html#operator_assign-value) operator, this is not recommended. [Section 9.4, “User-Defined Variables”](user-variables.html), explains why you should avoid doing this.

* []()

  [`=`](assignment-operators.html#operator_assign-equal)

  []()[]()[]()

   This operator is used to perform value assignments in two cases, described in the next two paragraphs.

   Within a [`SET`](set-variable.html) statement, `=` is treated as an assignment operator that causes the user variable on the left hand side of the operator to take on the value to its right. (In other words, when used in a [`SET`](set-variable.html) statement, `=` is treated identically to [`:=`](assignment-operators.html#operator_assign-value).) The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value). You can perform multiple assignments in the same [`SET`](set-variable.html) statement.

   In the `SET` clause of an [`UPDATE`](update.html) statement, `=` also acts as an assignment operator; in this case, however, it causes the column named on the left hand side of the operator to assume the value given to the right, provided any `WHERE` conditions that are part of the [`UPDATE`](update.html) are met. You can make multiple assignments in the same `SET` clause of an [`UPDATE`](update.html) statement.

   In any other context, `=` is treated as a [comparison operator](comparison-operators.html#operator_equal).

  ```
  mysql> SELECT @var1, @var2;
          -> NULL, NULL
  mysql> SELECT @var1 := 1, @var2;
          -> 1, NULL
  mysql> SELECT @var1, @var2;
          -> 1, NULL
  mysql> SELECT @var1, @var2 := @var1;
          -> 1, 1
  mysql> SELECT @var1, @var2;
          -> 1, 1
  ```

   For more information, see [Section 13.7.6.1, “SET Syntax for Variable Assignment”](set-variable.html), [Section 13.2.13, “UPDATE Statement”](update.html), and [Section 13.2.11, “Subqueries”](subqueries.html).