By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,911 Members | 480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,911 IT Pros & Developers. It's quick & easy.

To update a record using rowid in duplicate row.

P: 9
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
Mar 26 '08 #1
Share this Question
Share on Google+
17 Replies

Expert 100+
P: 785
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
Mar 26 '08 #2

P: 9
Thanks thats Perfect.....
Is there any way to store the rowid before updating.
Please guide me to get through the issue.
Mar 27 '08 #3

amitpatel66
Expert 100+
P: 2,367
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?
Mar 27 '08 #4

P: 9
Yes we have to store the record in log table. Please clarify me.
Mar 27 '08 #5

amitpatel66
Expert 100+
P: 2,367
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.
Mar 27 '08 #6

P: 9
I tried creating but the result does not getting updated could you please help me out.
Mar 27 '08 #7

amitpatel66
Expert 100+
P: 2,367
I tried creating but the result does not getting updated could you please help me out.

Could you please post what you tried for?
Mar 27 '08 #8

P: 9
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)
Mar 27 '08 #9

amitpatel66
Expert 100+
P: 2,367
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT SUBSTR('ABccSS123', 1,2) FROM dual;
  3.  
  4. SUBSTR('ABCCSS123',1,2)
  5. ------------------------------------------
  6. AB
  7.  
  8.  
Mar 28 '08 #10

P: 9
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
  1.  
  2. declare
  3. a employee_master.emp_svce_id%type;
  4. b employee_master.emp_svce_id%type;
  5. begin
  6. a:='&emp_svce_id';
  7. select emp_svce_id into a from employee_master where emp_code ='PRODCD';
  8. dbms_output.put_line('The a is'||a); // prints CCDB00123
  9. select substr('a',1,2) into b from employee_master where emp_code ='PRODCD';
  10. dbms_output.put_line('b is'||b); // prints only a [ need DB00123 ]
  11. end;
  12.  
Mar 28 '08 #11

amitpatel66
Expert 100+
P: 2,367
Try This:

Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. --a employee_master.emp_svce_id%TYPE;
  3. b employee_master.emp_svce_id%TYPE;
  4. BEGIN
  5. --a:='&emp_svce_id';
  6. --SELECT emp_svce_id INTO a FROM employee_master WHERE emp_code ='PRODCD';
  7. --dbms_output.put_line('The a is'||a); // prints CCDB00123
  8. SELECT SUBSTR(emp_svce_id,3) INTO b FROM employee_master WHERE emp_code ='PRODCD'; -- this will give you DB00123 in variable b
  9. dbms_output.put_line('b is'||b); -- prints only a [ need DB00123 ]
  10. END;
  11.  
NOTE: The single line Comment in PLSQL is -- and not //
Mar 28 '08 #12

P: 9
The mistake I did is passed 'a' instead of giving a. Thanks for guiding me...
Have a nice weekend..
Mar 28 '08 #13

amitpatel66
Expert 100+
P: 2,367
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.
Mar 28 '08 #14

P: 9
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
  1.  
  2. DECLARE
  3. a employee_master.emp_svce_id%TYPE;
  4. b employee_master.emp_svce_id%TYPE;
  5. c product_feature_instance.feature_code%type;
  6. d product_feature_instance.feature_value%type;
  7. e product_feature_instance.feature_value%type;
  8. BEGIN
  9. a:='&emp_svce_id';
  10. SELECT SUBSTR(emp_svce_id,3,10) INTO b FROM employee_master WHERE emp_code ='PRODCD'and emp_svce_id=a;
  11. dbms_output.put_line('b is'||b);
  12. e:=b;
  13. update employee_master set emp_value =e where prod_svce_id =a and emp_code ='PRODCD' and order_line_function ='RG';
  14. 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';
  15. dbms_output.put_line('emp_code'||c||'emp_value'||d);
  16. commit;
  17. end;
  18.  
Mar 28 '08 #15

amitpatel66
Expert 100+
P: 2,367
Small modifications to your code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. a employee_master.emp_svce_id%TYPE;
  4. b employee_master.emp_svce_id%TYPE;
  5. c product_feature_instance.feature_code%TYPE;
  6. d product_feature_instance.feature_value%TYPE;
  7. --e product_feature_instance.feature_value%TYPE;
  8. BEGIN
  9. a:='&emp_svce_id';
  10. SELECT SUBSTR(emp_svce_id,3,10) INTO b FROM employee_master WHERE emp_code ='PRODCD'AND emp_svce_id=a;
  11. dbms_output.put_line('b is'||b);
  12. 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
  13. COMMIT;
  14. --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';
  15. dbms_output.put_line('emp_code'||c||'emp_value'||d  );
  16. END;
  17.  
Mar 28 '08 #16

P: 9
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
Mar 28 '08 #17

amitpatel66
Expert 100+
P: 2,367
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. DECLARE
  3. a employee_master.emp_svce_id%TYPE;
  4. b employee_master.emp_svce_id%TYPE;
  5. c employee_master.emp_code%TYPE;
  6. d employee_master.emp_value%TYPE;
  7. BEGIN
  8. a:='&emp_svce_id';
  9. SELECT SUBSTR(emp_svce_id,3) INTO b FROM employee_master WHERE emp_code ='PRODCD'AND emp_svce_id=a;
  10. dbms_output.put_line('b is'||b);
  11. 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
  12. COMMIT;
  13. dbms_output.put_line('emp_code'||c||'emp_value'||d    );
  14. END;
  15.  
Mar 29 '08 #18

Post your reply

Sign in to post your reply or Sign up for a free account.