sql逐条更新表数据
来源:互联网 发布:每天读书 知乎 编辑:程序博客网 时间:2024/05/29 16:46
一开始,参考网上的逐条更新办法:
declare @yuyueskid varchar(40)
declare @shangkesj datetimedeclare @yuyuezt int
declare @i int
declare @j int
set @j=(select count(*) from yuyuesk)
set @i=1
while @i<=@j
begin
set @yuyueskid=(select yuyueskid from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyueskId from yuyuesk ) as sp where Row=@i)
set @shangkesj=(select shangkesj from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, shangkesj from yuyuesk ) as sp where Row=@i)
set @yuyuezt=(select yuyuezt from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyuezt from yuyuesk ) as sp where Row=@i)
update
yuyuesk
set
yuyuezt=4
where
yuyueskId=@yuyueskid and @yuyuezt=1 and @shangkesj<GETDATE()
set @i=@i+1
end
出现的问题是,虽然可以逐条更新,但是需要把表内的数据全部过一次,如果表内数据太多,效率会非常低。
然后改变思路,先把符合更新条件的数据插入临时表,然后再更新:
declare @yuyueskid varchar(40)
declare @shangkesj datetime
declare @yuyuezt int
declare @i int
declare @j int
select * into #yuyuesk from yuyuesk where yuyuezt=1
set @j=(select count(*) from #yuyuesk)
set @i=1
while @i<=@j
begin
set @yuyueskid=(select yuyueskid from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyueskId from #yuyuesk ) as sp where Row=@i)
set @shangkesj=(select shangkesj from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, shangkesj from #yuyuesk ) as sp where Row=@i)
set @yuyuezt=(select yuyuezt from ( select ROW_NUMBER() over (order by yuyueskid asc ) as Row, yuyuezt from #yuyuesk ) as sp where Row=@i)
update
yuyuesk
set
yuyuezt=4
where
yuyueskId=@yuyueskid and @yuyuezt=1 and @shangkesj<GETDATE()
set @i=@i+1
end
完美解决
- sql逐条更新表数据
- 逐条更新数据 sql
- 逐条更新数据 sql
- DataGridView数据更新至数据库-逐条更新
- Sql Server 存取过程读取表中数据,修改后再逐条插入
- mysql游标示例 mysql游标简易教程 mysql批量操作数据 mysql批量更新数据 mysql逐条更新数据
- SQL 更新数据填充表
- 数据错位更新 交错更新 表自己更新自己 sql
- Android逐条加载ListView,实现动画更新
- Android逐条加载ListView,实现动画更新
- Android逐条加载ListView,实现动画更新
- Android逐条加载ListView,实现动画更新
- 批量更新与逐条更新分块提交的一次实例:修改客户信息表700万条中50万条记录
- 数据批量插入与逐条插入分析
- txt格式数据的逐条处理
- Sql 跨服务器,表更新数据
- SQL语句:查询多表更新数据
- [SQL] 数据同步更新
- socket select
- typeid的使用方法
- VirtualBox 共享文件夹设置 及 开机自动挂载
- jQuery高级事件---模拟操作和命名空间
- CentOS上运行BeyondCompare
- sql逐条更新表数据
- 工具类之LocationUtils(定位工具类)
- eclipse 开发工具下载
- Spring+quartz实现定时任务实例
- springboot + birt Design Engine API,Report Engine API
- IntelliJ IDEA搭建SpringBoot应用完成的一个小Demo
- js判断浏览器信息大全
- TCPCopy的安装和使用
- 源码分析ArrayList/Vector/LinkedList