mysql - 如何为更新创建动态值？

Question

这是我当前的查询:(它是一个触发器)

UPDATE qanda SET closed = COALESCE(closed, 0) + 1 WHERE id = new.qanda_id

现在我需要让它更高级。目前它总是增加+1。我想加入 users 表并检查这些:

1. 如果role_id == 1，则+ 5
2. 如果声誉> 5000，则+ 5

我怎样才能做到这一点？

<小时/>

这就是我所做的。我想用 CASE WHEN 来做到这一点(如何？)

BEGIN

SELECT reputation, role_id INTO @reputation , @role_id FROM users WHERE id = new.user_id;

IF (@reputation >= 5000 OR role_id == 1) THEN
    UPDATE qanda SET closed = COALESCE(closed, 0) + 5 WHERE id = new.qanda_id;
ELSE
    UPDATE qanda SET closed = COALESCE(closed, 0) + 1 WHERE id = new.qanda_id;
END IF

END

Answer 1

update qanda q, users u 
set q.closed=COALESCE(q.closed, 0) + if((u.reputation >= 5000 OR u.role_id = 1),5,1)
where q.id = u.qanda_id