update关于IN的多行更新

why：

　　现在需要更新过期时间的oder的状态，而发现直接使用IN总是失败。如下：

失效order：

SELECT order_id FROM tsb_order WHERE type=1 AND (NOW()<start_time OR NOW()>end_time)

 

查询OK：

SELECT order_id, phase, state FROM tsb_order
WHERE tsb_order.order_id IN (SELECT order_id FROM tsb_order WHERE type=1 AND (NOW()<start_time OR NOW()>end_time)) 

 

更新失败：

UPDATE tsb_order SET state=-1
WHERE tsb_order.order_id IN (SELECT order_id FROM tsb_order WHERE type=1 AND (NOW()<start_time OR NOW()>end_time))

 

HOW:

1、关联更新

UPDATE (SELECT order_id FROM tsb_order WHERE type=1 AND (NOW()<start_time OR NOW()>end_time)) AS invalid INNER JOIN tsb_order
ON invalid.order_id = tsb_order.order_id
SET state=-1

 

2、使用EXISTS

UPDATE tsb_order SET state=-1
WHERE EXISTS (SELECT order_id FROM (SELECT order_id FROM tsb_order WHERE type=1 AND (NOW()<start_time OR NOW()>end_time)) AS invalid WHERE invalid.order_id=tsb_order.order_id)