473,890 Members | 1,370 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

To update a record using rowid in duplicate row.

9 New Member

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.

Mar 26 '08
17 12302
ganesh raj
9 New Member
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
  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;
Mar 28 '08 #11
2,367 Recognized Expert Top Contributor
Try This:

Expand|Select|Wrap|Line Numbers
  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;
NOTE: The single line Comment in PLSQL is -- and not //
Mar 28 '08 #12
ganesh raj
9 New Member
The mistake I did is passed 'a' instead of giving a. Thanks for guiding me...
Have a nice weekend..
Mar 28 '08 #13
2,367 Recognized Expert Top Contributor
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
ganesh raj
9 New Member
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
  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;
Mar 28 '08 #15
2,367 Recognized Expert Top Contributor
Small modifications to your code:

Expand|Select|Wrap|Line Numbers
  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;
Mar 28 '08 #16
ganesh raj
9 New Member
Its throwing error. The error details is given below.

Error details:
ERROR at line 1:
ORA-12899: value too large for column
"SCOTT"."EMP_MA STER"."EMP_VALU E" (actual: 10, maximum: 7)
ORA-06512: at line 13
Mar 28 '08 #17
2,367 Recognized Expert Top Contributor
Try This:

Expand|Select|Wrap|Line Numbers
  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;
Mar 29 '08 #18

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

Similar topics

by: Sandy | last post by:
I am trying to do the following: EXEC SQL UPDATE MY TABLE SET COL1 = :newValue WHERE COL1 = 0 AND ROWNUM = 1 ORDER BY COL2; (index on COL1, COL2)
by: Robert | last post by:
How can I query an existing table and update a field in each record in the table with the occurrence count of each record e.g. update field to 1 (= first record occurrence), update field to 2 for 2nd record occurrence i.e. first duplicate record), update the field to 3 for the 3rd record occurrence i.e. 2nd duplicate record Example for a duplicated record (field with value ABCD) Rec 1 ABCD 1 Rec 2 ABCD ...
by: Doug | last post by:
I've got a strongly-typed dataset with 2 related tables in it ("Staff" and "Roles"). I want to make a change to the parent Staff row and also to its child Role row. I'm having difficulty figuring out the best way to do this. First question: I can get myself a new instance of a row and make changes to it, but presumably that's then a separate object from the original dataset row. How do I reconcile my changes back to the dataset?
by: Louis LeBlanc | last post by:
Hey folks. I'm new to the list, and not quite what you'd call a DB Guru, so please be patient with me. I'm afraid the lead up here is a bit verbose . . . I am working on an application that uses very high volume DB transactions - in the order of tens of millions per day . . . Anyway, the current database which will remain nameless, but begins with O and rymes with debacle (sorta), has a problem with high volume work when it comes to...
by: Altemir | last post by:
I want to create an UPDATE statement that updates a value in a table in database A based on the results of an inner join between tables each residing in deifferent databases. What is the correct syntax for doing this? The following should give you an idea of what I'm trying to do: UPDATE A.dbo.tblCars SET A.dbo.tblCars.Car = 'Ferrari'
by: MLH | last post by:
Fields in MyTable: PostID PostDate RollQtyXfer RollDenomination RollCount37 RollCount23
by: FangQ | last post by:
hi I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index and is "unique". when I run
by: Steve Richter | last post by:
what is the standard way of using sql in C# to increment a counter column in a shared control table? ( example: the next order number ) I would like to sql select a row in a table and lock it. Then run code that assigns the new value to a column in the row. Then sql update the column in the row, releasing the lock. thanks, SqlDataReader rdr = null;
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.