Description:
When using the Update table Set ... statement, one should be using commas to separate multilple updates. (Example: Update x Set y=z, a=b, d=c Where ...) However, using AND or OR in place of the commas doesn't throw a syntax error. As a matter of fact, it succeeds with the wrong results. The first update is set to zero if numeric(or null if non-numeric) and the rest are ignored.
The documentation clearly states the Update syntax should use commas.
<snip>
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]
Multiple-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
<end snip>
However, since this succeeds, a developer who is not checking to make sure the result set is correct, will be in big trouble if this gets used in production. Quite frankly, that developer should be stung anyway because they didn't read the documentation. Nevertheless, it is still something that needs to be addressed and made into a syntax error.
How to repeat:
CREATE TABLE `x` (
`a` int(10) unsigned NOT NULL default 0,
`b` char(1) NOT NULL default '',
`c` smallint(5) unsigned default 0
)
INSERT INTO x VALUES(1, 'd', 2), (2, 'e', 3), (3, 'f', 4);
----First Test----
Update x Set a = 4 AND b = 'z' where c = 2;
----Results----
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> Select * from x;
+---+---+------+
| a | b | c |
+---+---+------+
| 0 | d | 2 |
| 2 | e | 3 |
| 3 | f | 4 |
+---+---+------+
----Second Test----
Update x Set b = 'z' and a = 2 where c = 2;
----Results----
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> Select * from x;
+---+---+------+
| a | b | c |
+---+---+------+
| 1 | 0 | 2 |
| 2 | e | 3 |
| 3 | f | 4 |
+---+---+------+
3 rows in set (0.01 sec)
Suggested fix:
Have parser throw syntax error if comma is not used in this case.
Description: When using the Update table Set ... statement, one should be using commas to separate multilple updates. (Example: Update x Set y=z, a=b, d=c Where ...) However, using AND or OR in place of the commas doesn't throw a syntax error. As a matter of fact, it succeeds with the wrong results. The first update is set to zero if numeric(or null if non-numeric) and the rest are ignored. The documentation clearly states the Update syntax should use commas. <snip> Single-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] Multiple-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] <end snip> However, since this succeeds, a developer who is not checking to make sure the result set is correct, will be in big trouble if this gets used in production. Quite frankly, that developer should be stung anyway because they didn't read the documentation. Nevertheless, it is still something that needs to be addressed and made into a syntax error. How to repeat: CREATE TABLE `x` ( `a` int(10) unsigned NOT NULL default 0, `b` char(1) NOT NULL default '', `c` smallint(5) unsigned default 0 ) INSERT INTO x VALUES(1, 'd', 2), (2, 'e', 3), (3, 'f', 4); ----First Test---- Update x Set a = 4 AND b = 'z' where c = 2; ----Results---- Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> Select * from x; +---+---+------+ | a | b | c | +---+---+------+ | 0 | d | 2 | | 2 | e | 3 | | 3 | f | 4 | +---+---+------+ ----Second Test---- Update x Set b = 'z' and a = 2 where c = 2; ----Results---- Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> Select * from x; +---+---+------+ | a | b | c | +---+---+------+ | 1 | 0 | 2 | | 2 | e | 3 | | 3 | f | 4 | +---+---+------+ 3 rows in set (0.01 sec) Suggested fix: Have parser throw syntax error if comma is not used in this case.