OceanBase AI Database supports common assignment operators.
Overview of logical operators
operator |
operand |
Meaning |
|---|---|---|
| := | unitary | Assigns the value on the right side of the operator to the variable on the left side. |
| = | unitary | Assignment (as part of a SET statement, or as part of a SET clause in an UPDATE statement) |
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 the value is a scalar). You can perform multiple assignments in the same SET statement. You can also perform multiple assignments within the same statement.
Unlike =, the := operator is never parsed as a comparison operator. This means you can use := to assign values to variables in any valid SQL statement, not just in SET statements.
For 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 cases:
In a
SETstatement,=is an assignment operator that assigns the value on the right side to the variable on the left side. Therefore, when used in aSETstatement,=behaves the same as:=.In the
SETclause of anUPDATEstatement,=also acts as an assignment operator. In this case, if theWHEREcondition of theUPDATEstatement is met, the value on the right side of the operator is assigned to the column on the left. You can perform multiple assignments within the sameSETclause of anUPDATEstatement.
In all other cases, = is treated as a relational 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