如何更新第一个条目的值，如果它不存在于任何行

Question

我有一个权重日志表:

id     userid   weight   is_start_weight
3      10       100      Y
5      10       98       N
7      11       120      N
9      11       115      N

每个用户必须在第一次进入'Y'时有开始重量标志，但有些人没有，我想设置它。

我可以这样选择用户:

SELECT p.userid
FROM weight_tracker_log p
WHERE NOT EXISTS (
    SELECT s.userid
    FROM weight_tracker_log s
    WHERE p.userid = s.userid AND s.is_start_weight = 'Y'
)

问题是如何将is_start_weight标志更新为'Y'?必须更新id最低的第一个用户条目

可能是我的选择是错误的，也许我应该更新每个用户的第一个条目'Y'?

Answer 1

您可以使用如下查询为每个用户设置最低id:

update weight_tracker_log wtl join
       (select userid, min(id) as minid
        from weight_tracker_log
        group by userid
       ) u
       on wtl.userid = u.userid and wtl.id = u.minid
    set is_start_weight = 'Y'
    where wtl.is_start_weight <> 'Y';

编辑:

如果现有用户在非最小id上可以有'Y'权重，则:

update weight_tracker_log wtl join
       (select userid, min(id) as minid,
               max(is_start_weight = 'Y') as has_y
        from weight_tracker_log
        group by userid
       ) u
       on wtl.userid = u.userid and wtl.id = u.minid and has_y = 0
    set is_start_weight = 'Y';

Answer 2

您要查找的是更新连接查询。试着

    update weight_tracker_log 
    inner join (
    SELECT min(p.id) id, p.userid
    FROM weight_tracker_log p
    WHERE NOT EXISTS (
        SELECT s.userid
        FROM weight_tracker_log s
        WHERE p.userid = s.userid AND s.is_start_weight = 'Y'
    )
    group by p.userid
    ) missing
    on weight_tracker_log.id = missing.id
    set weight_tracker_log.is_start_weight = 'Y'

。https://www.google.com.au/webhp?sourceid=chrome-instant&离子= 1,espv = 2, ie = utf - 8 # q = mysql % 20更新% 20加入