To update a record using rowid in duplicate row.

Hi,

want to write an plsql code to update a single record using rowid where all the rows are duplicated could someone help me in this case.

Prod_service_id Location feature ord
----------------------- ------------ ---------- ----
PP23456 Chennai Fast2 B77
PP23456 Chennai Fast2 B77

Now I need to update one of the record to madurai.

Thanks,
Ganesh

chaarmann

Expert

177 Posts

March 26th, 2008
05:15 PM
#2

Re: To update a record using rowid in duplicate row.

update our_table
set Location = 'madurai'
WHERE rowid in
(SELECT MIN(rowid)
FROM our_table
GROUP BY Prod_service_id, Location, feature, ord);

Quote:

amitpatel66

Moderator

1,884 Posts

March 27th, 2008
09:16 AM
#4

Re: To update a record using rowid in duplicate row.

Quote:

Thanks thats Perfect.....
Is there any way to store the rowid before updating.
Please guide me to get through the issue.

Could you please let us know if you would like to store the record that is getting updated in to some log table or something?

amitpatel66

Moderator

1,884 Posts

March 27th, 2008
11:57 AM
#6

Re: To update a record using rowid in duplicate row.

Quote:

Yes we have to store the record in log table. Please clarify me.

Create a trigger, before update on table1, take the old data and insert into a log table.

ganesh raj

Newbie

9 Posts

March 27th, 2008
03:26 PM
#9

Re: To update a record using rowid in duplicate row.

Thanks its working now one more thing is that I have many columns and in that I want to access an single record with unique id and cut the first two letters using the number in the query is it possible.

eg:
emp_id
----------
ABssCC123

I tried the below logic but its not working...
Trim(to_char(emp_id, 'A%'),2)

amitpatel66

Moderator

1,884 Posts

March 28th, 2008
05:04 AM
#10

Re: To update a record using rowid in duplicate row.

Quote:

Try this Query:

Expand|Select|Wrap|Line Numbers

	 
SELECT SUBSTR('ABccSS123', 1,2) FROM dual;
 
SUBSTR('ABCCSS123',1,2)

------------------------------------------

AB

ganesh raj

Newbie

9 Posts

March 28th, 2008
09:56 AM
#11

Re: To update a record using rowid in duplicate row.

This is the code Iam using in plsql where the emp_svce_id (employee service id) has to be printed which is CCDB00123. I want to cut the first two character and store DB00123 below is the script Iam trying can you please help me....

Expand|Select|Wrap|Line Numbers

	 
declare

a employee_master.emp_svce_id%type;

b employee_master.emp_svce_id%type;

begin

a:='&emp_svce_id';

select emp_svce_id into a from employee_master where emp_code ='PRODCD';

dbms_output.put_line('The a is'||a); // prints CCDB00123

select substr('a',1,2) into b from employee_master where emp_code ='PRODCD';

dbms_output.put_line('b is'||b); // prints only a [ need DB00123 ]

end;

Last edited by amitpatel66 : March 28th, 2008 at 10:09 AM. Reason: code tags

amitpatel66

Moderator

1,884 Posts

March 28th, 2008
10:10 AM
#12

Re: To update a record using rowid in duplicate row.

Try This:

Expand|Select|Wrap|Line Numbers

	
DECLARE

--a employee_master.emp_svce_id%TYPE;

b employee_master.emp_svce_id%TYPE;

BEGIN

--a:='&emp_svce_id';

--SELECT emp_svce_id INTO a FROM employee_master WHERE emp_code ='PRODCD';

--dbms_output.put_line('The a is'||a); // prints CCDB00123

SELECT SUBSTR(emp_svce_id,3) INTO b FROM employee_master WHERE emp_code ='PRODCD'; -- this will give you DB00123 in variable b

dbms_output.put_line('b is'||b); -- prints only a [ need DB00123 ]

END;

NOTE: The single line Comment in PLSQL is -- and not //

Last edited by amitpatel66 : March 28th, 2008 at 10:13 AM. Reason: Removed Unnecessary use of variable a

amitpatel66

Moderator

1,884 Posts

March 28th, 2008
10:50 AM
#14

Re: To update a record using rowid in duplicate row.

Quote:

The mistake I did is passed 'a' instead of giving a. Thanks for guiding me...
Have a nice weekend..

Thats right. And the use of vairalbe a was not required as it will occupy space for no reason. The sample code that i have shown you is the ideal way.

ganesh raj

Newbie

9 Posts

March 28th, 2008
11:59 AM
#15

Re: To update a record using rowid in duplicate row.

Here a is the unique id and this should be entered by the user so Iam using this and now one more thing is that I want to update the emp_value and for the particular emp_svce_id with the result from the below query

SELECT SUBSTR(emp_svce_id,3) INTO b FROM employee_master WHERE emp_code ='PRODCD'and emp_svce_id=a;

And want to print the emp_value and emp_code; will it be possible below is what I tried.

Expand|Select|Wrap|Line Numbers

	 
DECLARE

a employee_master.emp_svce_id%TYPE;

b employee_master.emp_svce_id%TYPE;

c product_feature_instance.feature_code%type;

d product_feature_instance.feature_value%type;

e product_feature_instance.feature_value%type;

BEGIN

a:='&emp_svce_id';

SELECT SUBSTR(emp_svce_id,3,10) INTO b FROM employee_master WHERE emp_code ='PRODCD'and emp_svce_id=a;

dbms_output.put_line('b is'||b);

e:=b;

update employee_master set emp_value =e where prod_svce_id =a and emp_code ='PRODCD' and order_line_function ='RG';

select emp_code, emp_value into c,d from employee_master where emp_svce_id  =a and emp_code ='PRODCD' and order_line_function ='RG';

dbms_output.put_line('emp_code'||c||'emp_value'||d  );

commit;

end;

Last edited by amitpatel66 : March 28th, 2008 at 12:44 PM. Reason: code tags

amitpatel66

Moderator

1,884 Posts

March 28th, 2008
01:00 PM
#16

Re: To update a record using rowid in duplicate row.

Small modifications to your code:

Expand|Select|Wrap|Line Numbers

	 
DECLARE

a employee_master.emp_svce_id%TYPE;

b employee_master.emp_svce_id%TYPE;

c product_feature_instance.feature_code%TYPE;

d product_feature_instance.feature_value%TYPE;

--e product_feature_instance.feature_value%TYPE;

BEGIN

a:='&emp_svce_id';

SELECT SUBSTR(emp_svce_id,3,10) INTO b FROM employee_master WHERE emp_code ='PRODCD'AND emp_svce_id=a;

dbms_output.put_line('b is'||b);

UPDATE employee_master SET emp_value =b WHERE prod_svce_id =a AND emp_code ='PRODCD' AND order_line_function ='RG' RETURNING emp_code,emp_value INTO c,d; -- This will work in case if only one record gets updated. Else you will need to use array for the RETURNING values

COMMIT;

--SELECT emp_code, emp_value INTO c,d FROM employee_master WHERE emp_svce_id  =a AND emp_code ='PRODCD' AND order_line_function ='RG';

dbms_output.put_line('emp_code'||c||'emp_value'||d    );

END;

ganesh raj

Newbie

9 Posts

March 28th, 2008
02:48 PM
#17

Re: To update a record using rowid in duplicate row.

Its throwing error. The error details is given below.

Error details:
declare
*
ERROR at line 1:
ORA-12899: value too large for column
"SCOTT"."EMP_MASTER"."EMP_VALUE" (actual: 10, maximum: 7)
ORA-06512: at line 13

amitpatel66

Moderator

1,884 Posts

March 29th, 2008
11:25 AM
#18

Re: To update a record using rowid in duplicate row.

Try This:

Expand|Select|Wrap|Line Numbers

	 
DECLARE

a employee_master.emp_svce_id%TYPE;

b employee_master.emp_svce_id%TYPE;

c employee_master.emp_code%TYPE;

d employee_master.emp_value%TYPE;

BEGIN

a:='&emp_svce_id';

SELECT SUBSTR(emp_svce_id,3) INTO b FROM employee_master WHERE emp_code ='PRODCD'AND emp_svce_id=a;

dbms_output.put_line('b is'||b);

UPDATE employee_master SET emp_value =b WHERE prod_svce_id =a AND emp_code ='PRODCD' AND order_line_function ='RG' RETURNING emp_code,emp_value INTO c,d; -- This will work in case if only one record gets updated. Else you will need to use array for the RETURNING values

COMMIT;

dbms_output.put_line('emp_code'||c||'emp_value'||d      );

END;