代码之家 › 专栏 › 技术社区 › medk

SQL联合更新

union sql-update sql mysql

medk · 技术社区 · 7 年前

我有一个SQL查询,它按rand进行限制和顺序选择,因此我得到每个 status 然后混合整个结果,得到一个随机结果,而不是每个块 地位 ,并按预期工作:

(SELECT * FROM `vicidial_list` WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "AR" LIMIT 1038)
UNION
(SELECT * FROM `vicidial_list` WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "CBHOLD" LIMIT 372)
UNION
(SELECT * FROM `vicidial_list` WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "NEW" LIMIT 3824)
UNION
(SELECT * FROM `vicidial_list` WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "PI" LIMIT 1405)
UNION
(SELECT * FROM `vicidial_list` WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "NRP" LIMIT 500)
UNION
(SELECT * FROM `vicidial_list` WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "RCAT" LIMIT 500)
ORDER BY RAND()

现在我想更新一下 list_id 到200,但这不起作用:

(UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "AR" LIMIT 1038)
UNION
(UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "CBHOLD" LIMIT 372)
UNION
(UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "NEW" LIMIT 3824)
UNION
(UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "PI" LIMIT 1405)
UNION
(UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "NRP" LIMIT 500)
UNION
(UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "RCAT" LIMIT 500)
ORDER BY RAND()

有什么建议吗?

谢谢

1 回复 | 直到 7 年前

Ike Walker 7 年前

只需运行每个 UPDATE 作为单独声明:

UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "AR" ORDER BY RAND() LIMIT 1038;
UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "CBHOLD" ORDER BY RAND() LIMIT 372;
UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "NEW" ORDER BY RAND() LIMIT 3824;
UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "PI" ORDER BY RAND() LIMIT 1405;
UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "NRP" ORDER BY RAND() LIMIT 500;
UPDATE `vicidial_list` SET list_id = 200 WHERE phone_number LIKE "7%" AND list_id NOT IN (500,1000) AND status = "RCAT" ORDER BY RAND() LIMIT 500;