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

MySQL根据另一个字段中的值更新字段

sql mysql

MoreScratch · 技术社区 · 7 年前

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;

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

思想?

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

是的,但只是因为 update 需要全表扫描。使用 like

所以,你可以:

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 .