mysql - UPDATE SET 不适用于 NOT IN (SELECT)？

Question

我的 SQL UPDATE 查询有问题:

UPDATE gon_nu.migration_threads 
    SET AuthorID = 461376 
WHERE AuthorID NOT IN (SELECT id AS AuthorID 
                       FROM prod_playing.users_userprofile 
                       WHERE username != 'OldPlayingUser');

它响应匹配项，但受影响的行数为 0。当 prod_playing.users_userprofile 中不存在 AuthorID 时，如何解决此问题，以便将 AuthorID 更新为 461376？

Answer 1

您的查询不起作用的一种可能是，当 username = 'OldPlayingUser' 时，users_userprofile.id 为 NULL。这表明此查询可能会执行您想要的操作:

UPDATE gon_nu.migration_threads 
    SET AuthorID = 461376 
    WHERE AuthorID NOT IN (SELECT id AS AuthorID 
                           FROM prod_playing.users_userprofile 
                           WHERE username <> 'OldPlayingUser' and id is not null
                          );

但是，您的问题表明您在 where 子句中可能有太多否定。在 where 条件不等于的子查询中使用 not in 的情况并不常见。也许这符合您的要求:

UPDATE gon_nu.migration_threads 
    SET AuthorID = 461376 
    WHERE AuthorID NOT IN (SELECT id AS AuthorID 
                           FROM prod_playing.users_userprofile 
                           WHERE username = 'OldPlayingUser' and id is not null
                          );