怎么批量的把一批记录的ID更新为当前ID的最大值顺序加1呢?
比如库里最大的ID是410000500
要把一批ID是00开头的都更新成 410000501,410000502,,,,。,
beckham 16:11:22
update irpt_departments set id= (select max(id)+rownum from irpt_departments) where id like '00%'
这么写好像不对 ,,,提示不是单组分组函数,,,
比如库里最大的ID是410000500
要把一批ID是00开头的都更新成 410000501,410000502,,,,。,
beckham 16:11:22
update irpt_departments set id= (select max(id)+rownum from irpt_departments) where id like '00%'
这么写好像不对 ,,,提示不是单组分组函数,,,
set id= (select max(id)
from irpt_departments) +rownum
where id like '00%'
执行后,报SQL未正常结束,请教一下是怎么回事呢?
DECLARE
a NUMBER(5);
b_max NUMBER;
CURSOR v_c IS SELECT * FROM irpt_departments where id like '00%' FOR UPDATE;
BEGIN
a:=1;
SELECT MAX(id) INTO b_max FROM irpt_departments;
FOR i IN v_c LOOP
UPDATE irpt_departments SET id=b_max+a WHERE CURRENT OF v_c;
a:=a+1;
END LOOP;
END;
多谢您的答复,我在PL/SQL里可以了
还要请教一下。我要在文件里那种批处理执行怎么写呢?
后面加commit;报错,有啥好办法?
--把下面的代码放到xx.sql中
DECLARE
a NUMBER(5);
b_max NUMBER;
CURSOR v_c IS SELECT * FROM irpt_departments where id like '00%' FOR UPDATE;
BEGIN
a:=1;
SELECT MAX(id) INTO b_max FROM irpt_departments;
FOR i IN v_c LOOP
UPDATE irpt_departments SET id=b_max+a WHERE CURRENT OF v_c;
a:=a+1;
END LOOP;
commit;
END;
/
--如果id列是主健或者unique的列,可以通过下面的方法,一条sql更新:UPDATE irpt_departments A
SET ID = (SELECT A
FROM (SELECT (SELECT MAX(ID) FROM irpt_departments) + ROWNUM A,
B.id
FROM irpt_departments B
WHERE id like '00%') C
WHERE C.id = A.id)
WHERE id like '00%';
commit;