To update a record using rowid in duplicate row.

[ganesh raj]

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]

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

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

[ganesh raj]

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

[amitpatel66]

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?

[ganesh raj]

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

[amitpatel66]

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]

I tried creating but the result does not getting updated could you please help me out.

[amitpatel66]

I tried creating but the result does not getting updated could you please help me out.

Could you please post what you tried for?

[ganesh raj]

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]

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)

Try this Query:

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

[ganesh raj]

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....

 
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;
 

[amitpatel66]

Try This:

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 //

[ganesh raj]

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

[amitpatel66]

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]

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.

 
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;
 

[amitpatel66]

Small modifications to your code:

 
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]

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]

Try This:

 
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;