update CBSUSROBJRELTAB t SET CHRGFLG=1 where exists ( select * from ( select *,row_number() over (partition by GAGEOBJID) rn from CBSUSROBJRELTAB )where rn=1 and GAGEOBJID=T.GAGEOBJID AND USRNM=t.USRNM )
UPDATE CVSUSROBJRELTAB A SET CHRGFLG = 1 WHERE EXISTS (SELECT 1 FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY GAGEOBJID ORDER BY GAGEOBJID) AS SEQ, ROWID AS RID FROM CVSUSROBJRELTAB) B WHERE B.SEQ = 1 AND B.RID = A.ROWID)
update table t set CHRGFLG=1 where exists(select 'Z' from (select GAGEOBJID,USRNM,CHRGFLG,count(decode(CHRGFLG,1,1,null)) from table group by GAGEOBJID,USRNM,CHRGFLG having count(*)=0) a, (select b.*,row_number() over(PARTITION BY GAGEOBJID ORDER BY GAGEOBJID) as rn from table b) c where a.GAGEOBJID=t.GAGEOBJID and a.USRNM=t.USRNM and c.rn=1) 应该没问题了
update CVSUSROBJRELTAB set CHRGFLG=1 where (GAGEOBJID,USRNM ) in ( SELECT GAGEOBJID,min(USRNM) from CVSUSROBJRELTAB where GAGEOBJID in ( SELECT GAGEOBJID from CVSUSROBJRELTAB group by GAGEOBJID having sum(decode (CHRGFLG,1,1,0))=0) group by GAGEOBJID)
UPDATE CVSUSROBJRELTAB A SET CHRGFLG = (SELECT '1' FROM (SELECT GAGEOBJID, MIN(USRNM) AS USRNM FROM CVSUSROBJRELTAB GROUP BY GAGEOBJID) B WHERE A.GAGEOBJID = B.GAGEOBJID AND A.USRNM = B.USRNM)
问题解决了吗? 以下测试过了 create table test( GAGEOBJID varchar(20), USRNM varchar(20), CHRGFLG varchar(20));insert into test select 'a','aa','1' from dual union select 'a','aa','2' from dual union select 'a','bb','12' from dual union select 'a','cc','13' from dual union select 'b','aa','2' from dual union select 'b','bb','12' from dual union select 'c','vv','4' from dual union select 'd','vv','4' from dualupdate test t set CHRGFLG=1 where (GAGEOBJID,USRNM) in ( select GAGEOBJID,min(USRNM) from test group by GAGEOBJID having count(decode(CHRGFLG,'1',1,null))=0 )
where exists
(
select * from
(
select *,row_number() over (partition by GAGEOBJID) rn from CBSUSROBJRELTAB
)where rn=1 and GAGEOBJID=T.GAGEOBJID AND USRNM=t.USRNM
)
SET CHRGFLG = 1
WHERE EXISTS (SELECT 1 FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY GAGEOBJID ORDER BY GAGEOBJID) AS SEQ,
ROWID AS RID
FROM CVSUSROBJRELTAB) B WHERE B.SEQ = 1 AND B.RID = A.ROWID)
update table t
set CHRGFLG=1
where exists(select 'Z' from (select GAGEOBJID,USRNM,CHRGFLG,count(decode(CHRGFLG,1,1,null)) from table
group by GAGEOBJID,USRNM,CHRGFLG having count(*)=0) a,
(select b.*,row_number() over(PARTITION BY GAGEOBJID ORDER BY GAGEOBJID) as rn from table b) c
where a.GAGEOBJID=t.GAGEOBJID and a.USRNM=t.USRNM and c.rn=1)
应该没问题了
我的做关联把这些去掉了
sql是让我写复杂了,看楼下的了!
SELECT GAGEOBJID,min(USRNM) from CVSUSROBJRELTAB where GAGEOBJID in (
SELECT GAGEOBJID from CVSUSROBJRELTAB group by GAGEOBJID having sum(decode (CHRGFLG,1,1,0))=0)
group by GAGEOBJID)
SET CHRGFLG = (SELECT '1'
FROM (SELECT GAGEOBJID, MIN(USRNM) AS USRNM
FROM CVSUSROBJRELTAB
GROUP BY GAGEOBJID) B
WHERE A.GAGEOBJID = B.GAGEOBJID
AND A.USRNM = B.USRNM)
以下测试过了
create table test(
GAGEOBJID varchar(20),
USRNM varchar(20),
CHRGFLG varchar(20));insert into test
select 'a','aa','1' from dual union
select 'a','aa','2' from dual union
select 'a','bb','12' from dual union
select 'a','cc','13' from dual union
select 'b','aa','2' from dual union
select 'b','bb','12' from dual union
select 'c','vv','4' from dual union
select 'd','vv','4' from dualupdate test t
set CHRGFLG=1
where (GAGEOBJID,USRNM) in (
select GAGEOBJID,min(USRNM) from test
group by GAGEOBJID
having count(decode(CHRGFLG,'1',1,null))=0
)