筛选本表的记录,更新符合要求的数据
--
修改根据表中字段记录的到达时间是否是48小时后的订单,如果是,则批量更新
update OrderBase set
OrderState = 8
from ( select * from dbo.OrderBase where 3 = datediff (DD,EndTime, getdate ()) and OrderState <> ' 6 ' and OrderState <> ' 7 ' and OrderState <> ' 8 ' and OrderState <> ' 9 ' ) as 筛选后的数据表 inner join OrderBase on 筛选后的数据表.Order_Id = OrderBase.Order_Id
-- 3 表示从次日起,48小时后订单过期
-- 修改时,当订单状态为已经完成或正常过期或用户取消或企业取消 时,不修改订单状态
-- 将 订单状态改为 8 用户过期
update OrderBase set
OrderState = 8
from ( select * from dbo.OrderBase where 3 = datediff (DD,EndTime, getdate ()) and OrderState <> ' 6 ' and OrderState <> ' 7 ' and OrderState <> ' 8 ' and OrderState <> ' 9 ' ) as 筛选后的数据表 inner join OrderBase on 筛选后的数据表.Order_Id = OrderBase.Order_Id
-- 3 表示从次日起,48小时后订单过期
-- 修改时,当订单状态为已经完成或正常过期或用户取消或企业取消 时,不修改订单状态
-- 将 订单状态改为 8 用户过期
其实上面这个SQL语句就简直就是脱什么放什么
update
OrderBase
set
OrderState = 8
where 1 = datediff (DD,EndTime, getdate ()) and OrderState <> ' 6 ' and OrderState <> ' 7 ' and OrderState <> ' 8 ' and OrderState <> ' 9 '
OrderState = 8
where 1 = datediff (DD,EndTime, getdate ()) and OrderState <> ' 6 ' and OrderState <> ' 7 ' and OrderState <> ' 8 ' and OrderState <> ' 9 '
原来上学的时候做过这类的题,只不过忘记了,现在回顾一下
关键字: from 一般都用where
from 后接表名与需更新表的关系