Home » SQL & PL/SQL » SQL & PL/SQL » how to update a column from table which it was took from input procedure
how to update a column from table which it was took from input procedure [message #412830] Sun, 12 July 2009 22:04 Go to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Hi all gurus, i got doubt here and i feel that u all gonna love this question.

I have a procedure which need to insert a table name, a column name that need to change and a column name as a reference.

example:

proc_A('table_1','column_1','column_ref')

Actually, this procedure will update all data in column_1 to another value which it took from column from another table. I've done so many ways but still didn't got the answer...

I would be appreciate if u all gurus out there cold help me here
Re: how to update a column from table which it was took from input procedure [message #412831 is a reply to message #412830] Sun, 12 July 2009 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
u is not a member of this forum

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: how to update a column from table which it was took from input procedure [message #412837 is a reply to message #412830] Mon, 13 July 2009 00:58 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
update all data in column_1 to another value which it took from column from another table


where you are passing the other table name in the procedure proc_A
Re: how to update a column from table which it was took from input procedure [message #412842 is a reply to message #412830] Mon, 13 July 2009 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your problem in writing the procedure?
Or do you expect to do your job?

Once more, please read OraFAQ Forum Guide.

Regards
Michel
Re: how to update a column from table which it was took from input procedure [message #412845 is a reply to message #412837] Mon, 13 July 2009 01:22 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Thanks for replying...actually in the procedure, i create a cursor to pass the value from the other table. Smile
Re: how to update a column from table which it was took from input procedure [message #412850 is a reply to message #412845] Mon, 13 July 2009 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: how to update a column from table which it was took from input procedure [message #412863 is a reply to message #412830] Mon, 13 July 2009 02:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The basic solution involves using the Execute Immediate command to execute a dynamic update statement build based on the parameters you pass in to the procedure.

If you want more detailed advice, show us what you've written so far.
Re: how to update a column from table which it was took from input procedure [message #412864 is a reply to message #412830] Mon, 13 July 2009 02:26 Go to previous messageGo to next message
psingh7777
Messages: 22
Registered: August 2007
Location: New Delhi
Junior Member

Also please let us know what error/problem you are facing in updating the column with anything in cursor from another table.
Re: how to update a column from table which it was took from input procedure [message #412997 is a reply to message #412830] Mon, 13 July 2009 23:03 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

Thanks for the reply guys, really appreaciate it

Ok, this is the code that i've done...

create or replace
PROCEDURE SCRAMBLE (tbl_name varchar2 default ' ',
l_name varchar2 default ' ',
num_id varchar2 default ' ',
l_count number default 0) IS


v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_empid varchar2(30) := num_id;
v_num number(10) := 1;
v_cnt number(10) := l_count;



BEGIN


if (v_tbl = ' ') or (v_cnt = 0) then

dbms_output.put_line('Table name and row number cannot null');
else

loop

if (v_lname <> ' ') and (v_empid <> ' ') then
execute immediate '
update '||v_tbl||' set
'||v_lname||'=(select last_name from table_A
where emplid = (select '||v_empid||' from
(select row_number() over (order by '||v_empid||') as n, '||v_empid||'
from '||v_tbl||')
where n= '||v_num||'))

where '||v_empid||' =(select '||v_empid||' from
(select row_number() over (order by '||v_empid||') as n, '||v_empid||'
from '||v_tbl||')
where n= '||v_num||')' ;

end if;

v_num := v_num + 1;


EXIT WHEN v_num -1 = v_cnt;
end loop;


if (v_lname <> ' ') and (v_empid <> ' ') then
dbms_output.put_line(v_cnt||' '||v_lname||' from table '||v_tbl||' have been scramble');
end if;

end if;


END SCRAMBLE;


The flow of this proc are like this:
1. it will change data in v_lname column to data in last name column which in table_A where v_empid are equal to emplid in table_A.

tbl_name = table name that contain the column
l_name = column name that the data in it will be change
num_id = column that contain id that same with table_A emplid
l_count = number of rows in tbl_name

actually this code works as expected, but i want to find here is there any possibilities not to use l_count as input??

i mean is there any way to procedure can knows the number of rows in input table name??

Anyway, thanks a lot for helping me..u all are superb!! Razz
Re: how to update a column from table which it was took from input procedure [message #412999 is a reply to message #412830] Mon, 13 July 2009 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Either you decided to not read the Posting Guidelines or decided to ignore the contents Posting Guidelines.

So I will not waste my time with any possible answer.
Re: how to update a column from table which it was took from input procedure [message #413007 is a reply to message #412997] Mon, 13 July 2009 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow all the previous posts.

Regards
Michel
Re: how to update a column from table which it was took from input procedure [message #413042 is a reply to message #412830] Tue, 14 July 2009 01:50 Go to previous messageGo to next message
carillpower
Messages: 48
Registered: April 2009
Location: malaysia
Member

yeah, already read it Smile
Re: how to update a column from table which it was took from input procedure [message #413049 is a reply to message #413042] Tue, 14 July 2009 02:52 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
carillpower wrote on Tue, 14 July 2009 08:50
yeah, already read it Smile

But not follow them Sad

Regards
Michel

Previous Topic: Performance ramifications Parallel hint.(merged 2) 10g
Next Topic: Strange trigger behavior with UPPER() LOWER()
Goto Forum:
  


Current Time: Thu Dec 08 22:16:54 CST 2016

Total time taken to generate the page: 0.18533 seconds