Assignment operator

OceanBase Database supports commonly used assignment operators.

Overview

Considerations

Assignment operator :=

The value on the right side of := can be a literal, a variable, or a valid expression, including the result of a query (provided that the value is a scalar). You can perform multiple assignments in the same SET statement. You can perform multiple assignments in the same statement.

Unlike =, the := operator is never resolved as a comparison operator. This means that you can use := to assign values to variables in any valid SQL statement, not just in SET statements.

Here is an example:

obclient> SELECT @var1 := 1, @var2;
+------------+-------+
| @var1 := 1 | @var2 |
+------------+-------+
|          1 | NULL  |
+------------+-------+
1 row in set

obclient> SELECT @var1:=COUNT(*) FROM t1;
+-----------------+
| @var1:=COUNT(*) |
+-----------------+
|               5 |
+-----------------+
1 row in set

obclient> SELECT @var1;
+-------+
| @var1 |
+-------+
|     5 |
+-------+
1 row in set

Assignment operator =

The = operator performs assignment in the following two scenarios:

• In a SET statement, = is considered an assignment operator and is used to assign the value on the right side of the operator to the variable on the left side. Therefore, when used in a SET statement, = behaves the same as :=.

• In the SET clause of an UPDATE statement, = also serves as an assignment operator. In this case, if the WHERE condition of the UPDATE statement is met, the value on the right side of the operator is assigned to the column on the left side. You can perform multiple assignments in the same SET clause of an UPDATE statement.

In all other scenarios, = is considered a comparison operator.

obclient>  SELECT * FROM t1;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

obclient> UPDATE t1 SET c1 = c1 + 1, c2 = c1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

obclient> SELECT * FROM t1;
+------+------+
| c1   | c2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set