我有唯一的列url varchar(4),并希望将其更新为唯一的随机字符串,但在重复的项目中遇到问题。因此,我想创建类似的循环,但要定位两次同一表的问题。
UPDATE IGNORE word SET url = (SELECT GROUP_CONCAT(SUBSTRING('1234567890qwertyuiopasdfghjklzxcvbnm' , 1+ FLOOR(RAND()*LENGTH('1234567890qwertyuiopasdfghjklzxcvbnm')) ,1) SEPARATOR '')
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS dummy_tbl)
WHERE EXISTS (SELECT url FROM word WHERE url IS NULL);
用单个查询填充我的列的最佳方法是什么?
您应该删除EXISTS
子句,然后直接更新NULL
行:
UPDATE
IGNORE word
SET url = ...
WHERE url IS NULL;
,您的完整查询将是:
UPDATE
IGNORE word
SET url =
(SELECT GROUP_CONCAT(SUBSTRING('1234567890qwertyuiopasdfghjklzxcvbnm',
1+ FLOOR(RAND()*LENGTH('1234567890qwertyuiopasdfghjklzxcvbnm')),1) SEPARATOR '')
FROM
(SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4) AS dummy_tbl)
WHERE url IS NULL;