mysql - 如何组合两个更新查询(MySQL)

Question

我有两个 SQL 查询:1. 获取下一行状态为“Active”的行，将其也设置为“Active”2. 从第一个sql 查询中删除前一行的状态。意思是，我想删除当前行的状态，将其移动到下一行。

1. 代码

UPDATE x AS a
    INNER JOIN (
        SELECT id, count(*) FROM x WHERE id=(select min(id) FROM ref_school_year where id > (SELECT ID FROM x WHERE status='START'))
    ) AS b on a.id = b.id
SET a.status = 'START'
WHERE a.id > 0

第二个 SQL 查询

UPDATE ref_school_year as a
INNER JOIN(
        SELECT * FROM ref_school_year
        WHERE status='START'
        ORDER BY id LIMIT 1
) as b on b.id = a.id
SET a.status=null
WHERE a.id > 0

例如。

Before Query:
-----------------------------
| Rows     |     Status     |
-----------------------------
| 1        |    Active      |
| 2        |    null        |

After Queries:
-----------------------------
| Rows     |     Status     |
-----------------------------
| 1        |    null        |
| 2        |    Active      |

Answer 1

create table test (id int, status varchar(10));
insert test(id, status) values(1, 'Active'), (2, NULL);

update test t inner join (select id from test where status = 'Active' or exists (...)) t1 on t.id = t1.id set t.status = if(t.status = 'Active', NULL, 'Active');

关键是使用 IF 函数同时更新两者。希望我是对的。