Home » SQL & PL/SQL » SQL & PL/SQL » select - update pl/sql sql (oracle 11g)
select - update pl/sql sql [message #560868] Tue, 17 July 2012 08:47 Go to next message
Elsa
Messages: 2
Registered: July 2012
Location: norway
Junior Member
0down votefavorite





I need to select v_col1, from table_x and that column gives me string that i need to put(update) into same rowid but into diffrent column(h_col2) in sama table table_x - sorry it seems easy but i am beginner....
tabl_x
rowid V_col1, h_col2 etc .....
1 672637263 GVRT1898
2 384738477 GVRT1876
3 263237863 GVRT1832

like in this example i need to put GVRT1898 (update) instead of 672637263 and i need to go into every row in this table_x and fix - like next line would be (rowid2 would be GVRT1876 instead of 384738477 Smile this table has 40000 lines like this and i need to loop for every rowid

THX for your responce Justin - this is a little more complex,

i have this string in h_col and need to take only GVRTnumber out and put into v_col - but it's hard becouse GVRTnumber is in various place in column see down here....

"E_ID"=X:"GVRT1878","RCode"=X:"156000","Month"=d:1,"Activate"=d:5,"Disp_Id"=X:"4673498","Tar"=X:"171758021";

2"E_ID"=X:"561001760","RCode"=X:"156000","Month"=d:1,"Activate"=d:5,"Disp_Id"=X:"GVRT1898","Tar"=X:"171758021";

h_col column have this number that i want but in various place like somethimes it's in this 600byte column it's in byte nr 156 - sometimes in 287 but the only unique is "GVRT...." how can i take that string and put it to v_col -

Can you show me how to write such SQL pl/sql ?

regards & thanks Elsa
Re: select - update pl/sql sql [message #560869 is a reply to message #560868] Tue, 17 July 2012 08:52 Go to previous messageGo to next message
BlackSwan
Messages: 22905
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: select - update pl/sql sql [message #560871 is a reply to message #560868] Tue, 17 July 2012 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
need to select v_col1, from table_x and that column gives me string that i need to put(update) into same rowid but into diffrent column(h_col2) in sama table table_x - sorry it seems easy but i am beginner....


UPDATE table_x SET h_col2 = v_col1;


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: select - update pl/sql sql [message #560878 is a reply to message #560871] Tue, 17 July 2012 10:38 Go to previous messageGo to next message
Littlefoot
Messages: 19694
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Elsa,

who is Justin?
Elsa
THX for your responce Justin - this is a little more complex,

How did he manage to reply to your message (you started the topic, there are no other messages posted before yours)?


OK, so you want to find GVRT values. Regular expressions might help here. I have created a table and put those long strings into V_COL1 column. Now, update:
SQL> update table_x set
  2    h_col = regexp_substr(v_col1, 'GVRT\d+');

2 rows updated.

SQL> column c1 format a50
SQL>
SQL> select substr(v_col1, 1, 50) c1,
  2         h_col
  3  from table_x;

C1                                                 H_COL
-------------------------------------------------- --------------------
"E_ID"=X:"GVRT1878","RCode"=X:"156000","Month"=d:1 GVRT1878
2"E_ID"=X:"561001760","RCode"=X:"156000","Month"=d GVRT1898

SQL>
icon7.gif  Re: select - update pl/sql sql [message #560883 is a reply to message #560878] Tue, 17 July 2012 10:55 Go to previous messageGo to next message
Elsa
Messages: 2
Registered: July 2012
Location: norway
Junior Member
Thanks "Littlefoot" u are genius Smile
Re: select - update pl/sql sql [message #560884 is a reply to message #560883] Tue, 17 July 2012 11:21 Go to previous message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read the forum guide and don't use IM/SMS speak.

Regards
Michel
Previous Topic: sql query
Next Topic: Oracle SQL Query - Delete
Goto Forum:
  


Current Time: Thu Oct 23 16:41:12 CDT 2014

Total time taken to generate the page: 0.08877 seconds