mysql - 在mysql的左连接中使用限制

Question

大家可以帮我解决这个不起作用查询吗？

UPDATE DB_1 
left join blacklist as blk 
    on DB_1.last_email=blk.email 
SET DB_1.sampling = ? 
WHERE blk.email IS NULL 
    and DB_1.sampling IS NULL 
LIMIT "+slot;

我当时需要更新 25k 行，但这样做不起作用。如何创建有效的查询？我正在考虑放弃左连接并使用 IN 进行连接。

Answer 1

您想要更新DB_1中不在黑名单中的行吗？

你可以尝试:

UPDATE 
    DB_1 
SET 
    DB_1.sampling = ? 
WHERE 
    NOT EXISTS
      ( SELECT *
        FROM blacklist AS blk 
        WHERE blk.email = DB_1.last_email
      ) 
ORDER BY  
    <something>
LIMIT 
    <whatever>

或者:

UPDATE 
        DB_1 AS upd
    JOIN
        ( SELECT t.PK
          FROM 
                  DB_1 AS t
              LEFT JOIN 
                  blacklist AS blk 
                      ON blk.email = t.last_email
          WHERE 
              blk.email IS NULL
          ORDER BY  
              <something>
          LIMIT 
              <whatever>
       ) AS lim
           ON lim.PK = upd.PK
SET 
    upd.sampling = ?