i want to update job along with '_1','_2','_3'… how can i?
Clash Royale CLAN TAG#URR8PPP
i want to update job along with '_1','_2','_3'… how can i?
This is the table EMP
select * from emp
--------------------
EMPNO JOB
---------- ---------
7698 MANAGER
7782 MANAGER
7499 SALESMAN
7521 SALESMAN
7654 SALESMAN
and i want it like
---------------------
EMPNO JOB
---------- ---------
7698 MANAGER_1
7782 MANAGER_2
7499 SALESMAN_3
7521 SALESMAN_4
7654 SALESMAN_5
.... ..........
.... ..........
how can i update it please explain @hiFi
– auro
Aug 11 at 14:05
On what basis do you want to increment those number suffixes? Please explain it clearly. Also, let us know your attempt.
– Kaushik Nayak
Aug 11 at 17:40
@Kaushik the following vaibhab's code is working as per my criteria.. thank u
– auro
Aug 12 at 14:09
It's good that it worked for you, but you should edit your question and show the expected output correctly to avoid confusion. Also,It is recommended that you compare the performance of all answers(especially Matthew's ) if you have some time and use the one which is most efficient for your data set.
– Kaushik Nayak
Aug 12 at 14:36
3 Answers
3
update emp a set JOB=(select JOB from (select JOB||'_'||rank() over (order by EMPNO ) JOB,EMPNO from emp )b where a.EMPNO =b.EMPNO )
where exists (select 1 from emp b where a.EMPNO=b.EMPNO)
will give _1,_2,_3,_4 so on order by empno
update emp a set JOB=(select JOB from (select JOB||'_'||rank() over (partition by JOB order by EMPNO ) JOB,EMPNO from emp )b where a.EMPNO =b.EMPNO )
where exists (select 1 from emp b where a.EMPNO=b.EMPNO)
will give _1,_2,_3,_4 so on job wise
its says ORA-12899: value too large for column "TKM"."EMP"."JOB" (actual: 10, maximum: 9)
– auro
Aug 11 at 16:02
@auro please check your table for datatype and size of field job
– Vaibhav
Aug 11 at 16:06
This will not generate values 3,4,5 for the salesmen. It will start over from 1
– Kaushik Nayak
Aug 11 at 17:46
@KaushikNayak yes you are right,sorry i forgot to remove partition by,my mistake
– Vaibhav
Aug 11 at 17:53
Unfortunately that too doesn't work. See this sqlfiddle.com/#!4/32b55/1 simply using
order by empno would have worked if op's data had empno's sorted based on their job, but it isn't. It is not possible to say what's the logic for ordering, unless the OP clarifies it.– Kaushik Nayak
Aug 11 at 18:21
order by empno
A MERGE will be way more efficient than that update.
MERGE
MERGE INTO emp t
USING ( SELECT empno, job || '_' || dense_rank() over ( order by empno ) new_job FROM emp) u
ON ( t.empno = u.empno )
WHEN MATCHED THEN UPDATE SET t.job = u.new_job;
But, it still doesn't give the desired output of OP.
– Kaushik Nayak
Aug 12 at 7:33
Thanks. I did not notice that the sequence was across jobs. I removed the
PARTITION BY from my answer. I know the OP's results are not sorted by empno. I am assuming that was a mistake in his post. The point of my answer is that MERGE is much more efficient than the UPDATE approach posted by Vaibhav.– Matthew McPeak
Aug 12 at 13:17
PARTITION BY
empno
MERGE
UPDATE
it will do :
select empno,ename||'_'||rownum from emp;
@auro is it working???
– nikhil sugandh
Aug 12 at 13:52
it is for only retrieve the data. bt i want to update like this
– auro
Aug 12 at 14:05
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Oracle has a rank operator
– hiFI
Aug 11 at 14:00