使用 update 或 select 语句的 MySQL 查询

Question

我们有一个表如下:

CREATE TABLE client_service_details(
  id bigint(20) NOT NULL AUTO_INCREMENT,
`client_details_id` bigint(20) NOT NULL,
`service_id` bigint(20) NOT NULL,
`list_index`  int(10)  NOT NULL default 0 ,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB ;


+----+-------------------+------------+------------+
| id | client_details_id | service_id | list_index |
+----+-------------------+------------+------------+
|  1 |               101 |          1 |          0 |
|  2 |               101 |          2 |          0 |
|  3 |               101 |          3 |          0 |
|  4 |               102 |          1 |          0 |
|  5 |               102 |          2 |          0 |
|  6 |               102 |          3 |          0 |
|  7 |               102 |          4 |          0 |
|  8 |               103 |          1 |          0 |
|  9 |               103 |          2 |          0 |
+----+-------------------+------------+------------+
9 rows in set (0.00 sec)

我们要根据 client_details_id、servce_id 更新列 (list_index)。或者，如果我们得到下面给出的选择语句，就可以了

输出应该如下:

+----+-------------------+------------+------------+
| id | client_details_id | service_id | list_index |
+----+-------------------+------------+------------+
|  1 |               101 |          1 |          0 |
|  2 |               101 |          2 |          1 |
|  3 |               101 |          3 |          2 |
|  4 |               102 |          1 |          0 |
|  5 |               102 |          2 |          1 |
|  6 |               102 |          3 |          2 |
|  7 |               102 |          4 |          3 |
|  8 |               103 |          1 |          0 |
|  9 |               103 |          2 |          1 |
+----+-------------------+------------+------------+

Answer 1

UPDATE client_service_details
SET list_index = service_id -1

这应该可行