一個更新語句
如有这样的表数据：
ID NAME RN
1 A
1 AA
1 AAA
2 B
2 BB
想更新成这样：
ID NAME RN
1 A 1
1 AA 2
1 AAA 3
2 B 1
2 BB 2
create table a as (select 1 id,'A' name,' ' rn from dual
union
select 1 id,'AA' name,' ' rn from dual
union
select 1 id,'AAA' name,' ' rn from dual
union
select 2 id,'B' name,' ' rn from dual
union
select 2 id,'BB' name,' ' rn from dual
)
一條一條的更新
CREATE OR REPLACE PROCEDURE PPP (P_ID NUMBER,
P_NAME VARCHAR2
)
IS
BEGIN
UPDATE A
SET RN= (SELECT RN1 FROM (SELECT ID,NAME,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID,NAME) RN1 FROM A)
WHERE ID=P_ID AND NAME=P_NAME)
WHERE ID=P_ID AND NAME=P_NAME;
COMMIT;
END ;
CREATE OR REPLACE FUNCTION FFFFF (P_ID NUMBER,
P_NAME VARCHAR2
)
RETURN VARCHAR2 IS
V_RN VARCHAR2(2);
BEGIN
PPP (P_ID,P_NAME);
SELECT RN INTO V_RN FROM A WHERE ID=P_ID AND NAME=P_NAME;
RETURN V_RN;
END;
也可以一起更新
UPDATE A
SET RN=(SELECT RN1 FROM (SELECT ID,NAME,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID,NAME) RN1 FROM A) B
WHERE A.NAME=B.NAME AND A.ID=B.ID)
WHERE EXISTS (SELECT RN1 FROM (SELECT ID,NAME,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID,NAME) RN1 FROM A) B
WHERE A.NAME=B.NAME AND A.ID=B.ID)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10768286/viewspace-83485/ ，如需转载，请注明出处，否则将追究法律责任。
