小弟是菜鸟,求SQL 语句一条
如何批量修改类似数据的指定记录
如preinstallstr表中存在如下记录
用户证号 业务 操作日期 步骤 结果 流水号
00001 MA 2007-01-23 09:02:23 6 0 NSLYIE0001
00001 MA 2007-01-23 09:45:12 6 0 NSLYIE0001
还有一些非关键字段我就不写了
我想批量修改存在这种情况的记录中操作日期较大的步骤改为0
如上我想把操作时间为2007-01-23 09:45:12的步骤改为0其他不变
如何批量操作~~急!!我能做的只是把这些用户找出来,
select id, opcode, workstep, status,serialno
from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
如何批量修改类似数据的指定记录
如preinstallstr表中存在如下记录
用户证号 业务 操作日期 步骤 结果 流水号
00001 MA 2007-01-23 09:02:23 6 0 NSLYIE0001
00001 MA 2007-01-23 09:45:12 6 0 NSLYIE0001
还有一些非关键字段我就不写了
我想批量修改存在这种情况的记录中操作日期较大的步骤改为0
如上我想把操作时间为2007-01-23 09:45:12的步骤改为0其他不变
如何批量操作~~急!!我能做的只是把这些用户找出来,
select id, opcode, workstep, status,serialno
from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
SELECT 1 from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
)
UPDATE preinstallstr SET "步骤"=0 WHERE id in(
SELECT id from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
)
SELECT id from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
)WHERE EXISTS(
SELECT 1 from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
)
SELECT id from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
)and EXISTS(
SELECT 1 from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2
)
from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2 ) tableaa set tableaa.操作日期 = '你要更新的东西'
where rownum = 1 order by tableaa.操作日期 desc
from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2 ) tableaa set tableaa.步骤 = '0'
where rownum = 1 order by tableaa.操作日期 desc
执行了你写SQL 报SQL语句未正确结束,且操作日期不再selelc 中,能否order by ?
UPDATE preinstallstr SET "步骤"=0 WHERE id =(
应该不能用=把?
用户证号 业务 操作日期 步骤 结果 流水号
00001 MA 2007-01-23 09:02:23 6 0 NSLYIE0001
00001 MA 2007-01-23 09:45:12 6 0 NSLYIE0001
还有一些非关键字段我就不写了
我想批量修改存在这种情况的记录中操作日期较大的步骤改为0
如上我想把操作时间为2007-01-23 09:45:12的步骤改为0其他不变 update preinstallstr
set 步骤=0
where (用户证号 业务 操作日期 步骤 结果 流水号 )
=
(select 用户证号,业务,max(操作日期),步骤,结果,流水号 from preinstallstr --只修改最大 操作日期 的记录
group by 用户证号,业务,操作日期,步骤,结果,流水号
having count(*)>=2 --一条记录的不做修改
)
执行内部的select找出来的记录也是0条
去掉操作日期找出来的记录数正确,但是update报错返回多行~
(select id, opcode, workstep, status,serialno
from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2);
我的preinstallstr表中的id,opcode,workstep,status,serialno都是一样的,
我改只能是参照操作时间来改~故上面的写法肯定会多修改数据。
set 步骤=0
where (用户证号 , 业务 ,操作日期 , 步骤 , 结果 , 流水号 )
=
(select 用户证号,业务,max(操作日期),步骤,结果,流水号 from preinstallstr --只修改最大 操作日期 的记录
group by 用户证号,业务,步骤,结果,流水号
having count(*)>=2 --一条记录的不做修改
)也就是找
用户证号 , 业务 ,操作日期 , 步骤 , 结果 , 流水号
都相等的记录,只有1条
update preinstallstr set "步骤"=0 where(id,"操作时间",opcode,workstep,status,serialno) in
(select id,max("操作时间") "操作时间" ,opcode, workstep, status,serialno
from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2);
我朋友给我提供了另外一种,也可以。
先见一个临时表
create table temp as
(select id, opcode,max(optime) optime, workstep, status,serialno
from preinstallstr
where workstep = '6'
and status = '0'
group by id, opcode, workstep, status,serialno
having count(*) >= 2);
这样就把相应的要修改的记录写到temp表中
然后update preinstallstr c
set workstep = 0
where exists (select *
from temp a, preinstallstr b
where a.optime = b.optime
and c.optime = b.optime
and a.serialno=b.serialno
and a.serialno=c.serialno)