mysql update 不能用select 作为条件吗

发布网友发布时间：2022-05-02 09:12

共2个回答

懂视网时间：2022-05-02 13:33

UPDATE `WarningSendMail` AS alias_1 INNER JOIN (SELECT * FROM `WarningSendMail` WHERE flag=1 AND topic_id LIKE "%2267%") AS alias_2 SET alias_1.topic_id = REPLACE(alias_1.topic_id,‘|2267‘,‘‘) WHERE alias_1.id = alias_2.id



update WarningSendMail set topic_id= REPLACE(topic_id,‘|2267‘,‘‘) WHERE id IN ( SELECT id FROM WarningSendMail WHERE flag=1 AND topic_id LIKE "%2267%" );

Mysql下SELECT的结果集作为UPDATE的WHERE条件时的SQL语法

标签：select set sel mail warnings and end highlight 条件

热心网友时间：2022-05-02 10:41

sql server中，我们可是使用以下update语句对表进行更新：
update a set a.xx= (select yy from b) ;
但是在mysql中，不能直接使用set select的结果，必须使用inner join：
update a inner join (select yy from b) c set a.xx = c.yy

例：
update mb_tariff a inner join
mb_tariff_temp b set a.payment = b.payment
where a.mybus_id = b.mybus_id
and a.tariff_id = b.tariff_id

自行示例：
UPDATE USER INNER JOIN (SELECT id,account FROM USER) c SET user.phone=c.account WHERE user.id = c.id