mysql - 为什么这个看似合法的 MySQL 查询会出错？

Question

首先，我有一个名为 goals

的表

+----+-----------+---------------------+---------------------+
| id | member_id | created_at          | updated_at          |
+----+-----------+---------------------+---------------------+
|  8 |         4 | 2016-08-08 15:36:06 | 2016-08-08 15:36:06 |
|  9 |         4 | 2016-08-08 15:38:37 | 2016-08-08 15:38:37 |
| 10 |         4 | 2016-08-08 15:44:13 | 2016-08-08 15:44:13 |
| 11 |         4 | 2016-08-08 16:45:14 | 2016-08-08 16:45:14 |
| 12 |         4 | 2016-08-08 16:54:10 | 2016-08-08 16:54:10 |
| 13 |         4 | 2016-08-08 17:04:13 | 2016-08-08 17:04:13 |
| 14 |         4 | 2016-08-08 17:08:09 | 2016-08-08 17:08:09 |
| 15 |         4 | 2016-08-08 17:11:17 | 2016-08-08 17:11:17 |
| 16 |         4 | 2016-08-08 17:16:53 | 2016-08-08 17:16:53 |
| 17 |         4 | 2016-08-08 17:19:39 | 2016-08-08 17:19:39 |
| 18 |         4 | 2016-08-08 17:30:53 | 2016-08-08 17:30:53 |
| 19 |         4 | 2016-08-08 17:37:27 | 2016-08-08 17:37:27 |
| 20 |         4 | 2016-08-09 09:32:09 | 2016-08-09 09:32:09 |
| 21 |         4 | 2016-08-09 12:08:29 | 2016-08-09 12:08:29 |
| 22 |         4 | 2016-08-09 12:16:51 | 2016-08-09 12:16:51 |
| 23 |         4 | 2016-08-09 12:27:48 | 2016-08-09 12:27:48 |
| 24 |         4 | 2016-08-09 12:41:59 | 2016-08-09 12:41:59 |
+----+-----------+---------------------+---------------------+
# ... other rows omitted for brevity

然后还有一个名为goal_cycles的表

+----+---------+---------------------+---------------------+--------------+
| id | goal_id | start_date          | end_date            | cycle_status |
+----+---------+---------------------+---------------------+--------------+
| 14 |       8 | 2016-08-09 15:35:59 | 2016-08-18 15:35:59 | active       |
| 15 |       9 | 2016-08-09 15:38:33 | 2016-08-13 15:38:33 | active       |
| 16 |      10 | 2016-08-09 15:44:11 | 2016-08-11 15:44:11 | active       |
| 17 |      11 | 2016-08-09 16:45:11 | 2016-08-14 16:45:11 | active       |
| 18 |      12 | 2016-08-09 16:54:08 | 2016-08-13 16:54:08 | active       |
| 19 |      13 | 2016-08-09 17:04:11 | 2016-08-13 17:04:11 | active       |
| 20 |      14 | 2016-08-09 17:08:06 | 2016-08-12 17:08:06 | active       |
| 21 |      15 | 2016-08-09 17:11:13 | 2016-08-10 17:11:13 | active       |
| 22 |      16 | 2016-08-09 17:16:50 | 2016-08-10 17:16:50 | active       |
| 23 |      17 | 2016-08-09 17:19:36 | 2016-08-16 17:19:36 | active       |
| 24 |      18 | 2016-08-09 17:30:50 | 2016-08-12 17:30:50 | active       |
| 25 |      19 | 2016-08-09 17:37:23 | 2016-08-12 17:37:23 | active       |
| 26 |      20 | 2016-08-10 09:32:06 | 2016-08-14 09:32:06 | passive      |
| 27 |      21 | 2016-08-10 12:08:26 | 2016-08-19 12:08:26 | passive      |
| 28 |      22 | 2016-08-10 12:16:48 | 2016-08-14 12:16:48 | passive      |
| 29 |      23 | 2016-08-10 12:27:44 | 2016-08-14 12:27:44 | passive      |
| 30 |      24 | 2016-08-10 12:41:54 | 2016-08-19 12:41:54 | passive      |
+----+---------+---------------------+---------------------+--------------+
# ... other rows omitted for brevity

任务:我需要为所有目标更新goal_cycles，其中memberId = 4 并且其中 cycle_status = active 以下列方式进行；

1. 更新 cycle_status = passive
2. 设置 start_date = start_date + 3days
3. 设置 end_date = end_date + 3days

所以我写了下面的 MySQL 查询；

update goal_cycles gc 
    set gc.cycle_status = 'passive', 
    set gc.start_date = DATE_ADD(gc.start_date, INTERVAL 3 DAY), 
    set gc.end_date = DATE_ADD(gc.end_date, INTERVAL 3 DAY) 
where gc.goal_id in (select g.id from goals g where g.member_id = 4)
and gc.cycle_status = 'active'

这在我看来像是一个合法的mysql 查询，但我收到以下错误消息:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set gc.start_date = DATE_ADD(gc.start_date, INTERVAL 3 DAY),
set gc.end_date = ' at line 3

请MySQL专家指出这个查询到底有什么问题，以及如何根据任务要求正确修复它。

Answer 1

你重复了 set。它应该在查询中只出现一次:

update table foo
    set field1 = value1,
        field2 = value2,
        field3 = value3,
        etc...

所以，不，这不是有效的语法。