Assignment operators

OceanBase Database supports general assignment operators.

Overview

Description

Assignment operator (:=)

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

Unlike =, the := operator is not parsed as a comparison operator. This means that you can use the := operator to assign a value to a variable in any valid SQL statement, not just in a SET statement.

Sample code:

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 (=)

You can use the = operator for value assignments in the following two cases:

• Assign the value on the right side of the = operator to the variable on its left in a SET statement. In other words, = and := are equivalent in a SET statement.

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

The = operator is used as a comparison operator in all other cases.

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