UPDATE scrm_cp_user SET is_valid_ = 0 WHERE external_user_id_ NOT IN <foreach collection="cpUserUserInfoVOList" item="item" separator="," open="(" close=")"> #{item.externalUserId} </foreach> AND NOT EXISTS ( SELECT 1 FROM scrm_cp_user_user_binding scuub WHERE scuub.cp_user_id_ = scrm_cp_user.id_ AND scuub.is_valid_ = 1 ) ,in超过1000如何解决
时间: 2023-12-14 17:37:09 浏览: 29
如果 IN 子句中的值超过1000个,可以使用分批次处理来解决。例如,您可以将 IN 子句拆分为多个子句,每个子句包含1000个值。然后,您可以使用 UNION ALL 运算符将这些子句组合起来。以下是一个示例代码:
```
UPDATE scrm_cp_user
SET is_valid_ = 0
WHERE external_user_id_ NOT IN (
SELECT external_user_id_
FROM (
SELECT external_user_id_
FROM cpUserUserInfoVOList
ORDER BY external_user_id_
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY
UNION ALL
SELECT external_user_id_
FROM cpUserUserInfoVOList
ORDER BY external_user_id_
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY
-- 继续添加子句,每个子句包含1000个值
) AS subquery
)
AND NOT EXISTS (
SELECT 1
FROM scrm_cp_user_user_binding scuub
WHERE scuub.cp_user_id_ = scrm_cp_user.id_
AND scuub.is_valid_ = 1
)
```
在此示例中,我们将 cpUserUserInfoVOList 表中的值拆分为多个子句,每个子句包含1000个值。我们使用 OFFSET 和 FETCH NEXT 子句来指定每个子句的范围。最后,我们使用 UNION ALL 运算符将这些子句组合在一起。