MySQL的一些更新、剔除方法_mysql

1. 关联表更新

mysql关联多表进行update更新操作

UPDATE Track INNER JOIN MV ON Track.trkid=MV.mvid SET Track.is_show=MV.is_show WHERE trkid<6

 等同于

UPDATE Track,MV SET Track.is_show=MV.is_show WHERE Track.trkid=MV.mvid and trkid<6

  实例：

UPDATE erp$pro$category_mapping cm JOIN  erp$pro$category_operation co ON cm.category_operation_id = co.category_id SET cm.STATUS = 2 WHERE (co.category_path LIKE '12/%' OR co.category_id = 12) ;

参考文章：http://blog.sina.com.cn/s/blog_4c197d420101aer2.html

2. 关联表删除

DELETE cm FROM erp$pro$category_mapping cm JOIN erp$pro$category_operation cp WHERE cp.channel_id = 11 AND  cp.LEVEL <> 1;

3. 更新条件where in条件中有子查询时的写法

UPDATE erp$pro$sku_channel_relation SET STATUS = 1 WHERE product_sku_id IN (    SELECT product_sku_id     FROM (  --（where条件有子查询无法更新的解决办法） SELECT DISTINCT t.product_sku_id FROM erp$pro$product_sku t JOIN erp$pro$product p ON t.product_id = p.product_id WHERE t.STATUS = 1  AND p.is_m2b_product IS  NULL AND t.sku_type >= 0 AND t.is_gift = 0 AND t.product_sku_id  IN (SELECT product_sku_id FROM erp$pro$sku_channel_relation  WHERE channel_id = 9 AND STATUS = 2)     ) tt ) AND channel_id = 9 AND STATUS = 2; 

最开始我定成如下SQL时总是报Error Code:1093错误：

UPDATE erp$pro$sku_channel_relation SET STATUS = 1 WHERE product_sku_id IN (    SELECT DISTINCT t.product_sku_id FROM erp$pro$product_sku t JOIN erp$pro$product p ON t.product_id = p.product_id WHERE t.STATUS = 1  AND p.is_m2b_product IS  NULL AND t.sku_type >= 0 AND t.is_gift = 0 AND t.product_sku_id  IN (SELECT product_sku_id FROM erp$pro$sku_channel_relation  WHERE channel_id = 9 AND STATUS = 2)    ) AND channel_id = 9 AND STATUS = 2; 

4. 关联表更新条件中有子查询时写法

UPDATE  erp$pro$sku_channel_relation a JOIN erp$pro$product_sku t ON a.product_sku_id=t.product_sku_id JOIN erp$pro$product p ON t.product_id = p.product_id  SET a.STATUS = 1 WHERE a.channel_id=9 AND a.STATUS = 2 AND t.STATUS = 1  AND p.is_m2b_product IS  NULL AND t.sku_type >= 0 AND t.is_gift = 0  AND t.product_sku_id  IN ( SELECT product_sku_id FROM ( --In 查询方式的解决办法 SELECT product_sku_id FROM erp$pro$sku_channel_relation  WHERE channel_id = 9 AND STATUS = 2 ) tt ); 

 最开始的写法，没有在子查询外再包一层查询的时候总是报Error Code:1093错误：

UPDATE  erp$pro$sku_channel_relation a JOIN erp$pro$product_sku t ON a.product_sku_id=t.product_sku_id JOIN erp$pro$product p ON t.product_id = p.product_id  SET a.STATUS = 1 WHERE a.channel_id=9 AND a.STATUS = 2 AND t.STATUS = 1  AND p.is_m2b_product IS  NULL AND t.sku_type >= 0 AND t.is_gift = 0  AND t.product_sku_id  IN (  SELECT product_sku_id FROM erp$pro$sku_channel_relation  WHERE channel_id = 9 AND STATUS = 2  ); 

MySQL 1093错误参考文章：http://www.2cto.com/database/201308/236323.html