我要更新一个表,这个表的结构是:
A B C
a01 b01 xx
a01 b02 xx
a02 b03 xx
a02 b04 xx
a03 b05 xx
.. .. ..
我想更新B列,他是按A列的值来分组更新的,更新成:
A B C
a01 1 xx
a01 2 xx
a02 1 xx
a02 2 xx
a03 1 xx
.. .. ..
这该怎么做呢? 各位高手帮帮忙! 尽量详细些,很急,多谢!!
...全文
6619打赏收藏
SQL语句(更新问题),很急!多谢
我要更新一个表,这个表的结构是: A B C a01 b01 xx a01 b02 xx a02 b03 xx a02 b04 xx a03 b05 xx .. .. .. 我想更新B列,他是按A列的值来分组更新的,更新成: A B C a01 1 xx a01 2 xx a02 1 xx a02 2 xx a03 1 xx .. .. .. 这该怎么做呢? 各位高手帮帮忙! 尽量详细些,很急,多谢!!
SQL> Update t_tempplmx aa
2 Set mx_bh = (select row_number() over(partition by pl_bh order by pl_bh)
3 From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
4 ;
From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
*
ERROR 位于第 3 行:
ORA-00933: SQL 命令未正确结束
sorry,试试这个。
Update t_tempplmx aa
Set mx_bh = (select row_number() over(partition by pl_bh order by pl_bh)
From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
SQL> Update t_tempplmx aa
2 Set mx_bh = (select row_number() over(partition by pl_bh)
3 From t_tempplmx where rowid =aa.rowid) Order By pl_bh,mx_bh
4 ;
Set mx_bh = (select row_number() over(partition by pl_bh)
*
ERROR 位于第 2 行:
ORA-30485: 在窗口说明中丢失 ORDER BY 表达式
SQL> Update t_tempplmx
2 Set mx_bh = (select row_number() over(partition by pl_bh Order By pl_bh)
3 From t_tempplmx where t_tempplmx.rowid = t_tempplmx.rowid Order By pl_bh)
4 ;
From t_tempplmx where t_tempplmx.rowid = t_tempplmx.rowid Order By pl_bh)
*
ERROR 位于第 3 行:
ORA-00907: 缺少右括号
create table TEST2(A VARCHAR2(10),B VARCHAR(5),C VARCHAR2(10));
INSERT INTO TEST2 VALUES('a01','b01','xx');
INSERT INTO TEST2 VALUES('a01','b02','xx');
INSERT INTO TEST2 VALUES('a02','b01','xx');
INSERT INTO TEST2 VALUES('a03','b01','xx');
create or replace procedure updateTest2ColB
is
m_a varchar2(10);
m_cnt number(10);
cursor cu is select a,b,c from test2 for update;
c cu%ROWTYPE;
begin
m_a:=' ';
m_cnt:=0;
open cu;
LOOP
FETCH cu into c;
EXIT WHEN cu%NOTFOUND; -- process the data
if m_a<>c.a then
m_cnt:=1;
m_a:=c.a;
else
m_cnt:=m_cnt+1;
end if;
update test2 set b=m_cnt where CURRENT OF cu;
END LOOP;
commit;
close cu;
end;
/