mysql - MySQL 根据另一个字段中的值更新字段
问题描述
我需要根据另一个字段中是否存在字符串来更新字段。我需要为数百个搜索字符串执行此操作。这是我迄今为止所拥有的:
SET SQL_SAFE_UPDATES=0;
UPDATE business_table
SET `chain` = '7-Eleven'
WHERE business_name LIKE BINARY '%7-Eleven%' OR business_name LIKE
BINARY '%7 Eleven%';
UPDATE business_table
SET `chain` = 'A&W'
WHERE business_name LIKE '%A & W%' OR business_name LIKE '%A&W%';
UPDATE business_table
SET `chain` = 'Blenz Coffee'
WHERE business_name LIKE BINARY '%Blenz Coffee%';
UPDATE business_table
SET `chain` = 'Booster Juice'
WHERE business_name LIKE BINARY '%Booster Juice%';
UPDATE business_table
SET `chain` = 'Boston Pizza'
WHERE business_name LIKE BINARY '%Boston Pizza%';
SET SQL_SAFE_UPDATES=1;
有没有更有效的方法来做到这一点?我尝试在不禁用 SQL_SAFE-UPDATES 的情况下将其放入存储过程中,如下所示:
UPDATE business_table b
SET `chain` = '7-Eleven'
WHERE b.id = (SELECT id FROM b WHERE business_name LIKE BINARY '%7-Eleven%' OR business_name LIKE BINARY '%7 Eleven%');
我收到此错误:
Error Code: 1093. You can't specify target table 'b' for update in FROM clause
所以我理解这个错误是什么,但我不知道如何解决这个问题;有数百个 UPDATE 语句。
想法?
解决方案
是的,但这只是因为您update
的 s 需要全表扫描。like
在模式开头使用with 通配符会阻止任何有用的索引。
所以,你可以这样做:
UPDATE business_table
SET chain = (CASE WHEN business_name LIKE BINARY '%7-Eleven%' OR business_name LIKE
BINARY '%7 Eleven%' THEN '7-Eleven'
WHEN (business_name LIKE '%A & W%' OR business_name LIKE '%A&W%') THEN 'A&W'
. . .
END)
WHERE (business_name LIKE BINARY '%7-Eleven%' OR business_name LIKE
BINARY '%7 Eleven%') OR
(business_name LIKE '%A & W%' OR business_name LIKE '%A&W%') OR
. . .
您需要填写 中的其余条件WHERE
和 中的条件CASE
。