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

);

 

    关联表更新例子

   

UPDATE erp$act$activity_sku acts JOIN (
	SELECT p.brand_id, p.mft_id, pc.category_id, ps.sku_number FROM erp$pro$product  p
	JOIN erp$pro$product_sku ps ON ps.product_id = p.product_id
	JOIN erp$pro$product_category pc ON p.product_id = pc.product_id
	JOIN erp$act$activity_sku  ask ON ask.sku_number = ps.sku_number
	WHERE ask.sku_number IS NOT NULL
) t ON acts.sku_number = t.sku_number
SET acts.brand_id = t.brand_id , acts.mft_id = t.mft_id, acts.category_id = t.category_id

 

 

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