OraFAQ Forum: SQL & PL/SQL » select

Home » SQL & PL/SQL » SQL & PL/SQL » select - update pl/sql sql (oracle 11g)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

select - update pl/sql sql [message #560868]

Tue, 17 July 2012 08:47

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

Report message to a moderator

Re: select - update pl/sql sql [message #560869 is a reply to message #560868]

Tue, 17 July 2012 08:52

BlackSwan
Messages: 20108
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/

Report message to a moderator

Re: select - update pl/sql sql [message #560871 is a reply to message #560868]

Tue, 17 July 2012 08:55

Michel Cadot
Messages: 54226
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

Report message to a moderator

Re: select - update pl/sql sql [message #560878 is a reply to message #560871]

Tue, 17 July 2012 10:38

Littlefoot
Messages: 16995
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>

Report message to a moderator