mysql - 如何将两条记录的单元格值加 1

Question

我正在尝试编写一个MySQL查询，将两条记录的点(属性)增加1，一条记录x_id = 1且age = 57，第二条记录y_id = 13和age = 36，我我正在尝试做这样的事情:

update myTable set points = points+1
where (x_id,age)=(1,57) and (y_id,age)=(13,36);

但这不起作用，请帮忙。

Answer 1

试试这个

MariaDB [t]> CREATE TABLE myTable(points INT, age INT, x_id INT, y_id INT);
Query OK, 0 rows affected (0.03 sec)

MariaDB [t]> INSERT INTO myTable VALUES(1, 57, 1, NULL);
Query OK, 1 row affected (0.00 sec)

MariaDB [t]> INSERT INTO myTable VALUES(1, 36, NULL, 13);
Query OK, 1 row affected (0.01 sec)

MariaDB [t]> SELECT * FROM myTable;
+--------+------+------+------+
| points | age  | x_id | y_id |
+--------+------+------+------+
|      1 |   57 |    1 | NULL |
|      1 |   36 | NULL |   13 |
+--------+------+------+------+
2 rows in set (0.00 sec)

MariaDB [t]> UPDATE myTable SET points = points + 1 WHERE (x_id = 1 AND age = 57) OR (y_id = 13 AND age = 36);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [t]> 
MariaDB [t]> SELECT * FROM myTable;
+--------+------+------+------+
| points | age  | x_id | y_id |
+--------+------+------+------+
|      2 |   57 |    1 | NULL |
|      2 |   36 | NULL |   13 |
+--------+------+------+------+
2 rows in set (0.00 sec)