ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一個更新語句

一個更新語句

原创 Linux操作系统 作者:jack198409 时间:2007-11-29 00:00:00 0 删除 编辑

如有这样的表数据:
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博客 ” ,链接:http://blog.itpub.net/10768286/viewspace-83485/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORACLE 查看权限
请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    284817