Home » SQL & PL/SQL » SQL & PL/SQL » Updating columns of one table based on other table (oracle 10g)
Updating columns of one table based on other table [message #346360] Mon, 08 September 2008 06:04 Go to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi All,
I have two tables and I upload data from an external source in both the tables.
Let the two tables be A and B.
Now the scenario is as follows.
First I upload data from an external source into table A.
Then I upload data from external source into table B. But as soon as I upload data into table B I want that depending on certain conditon of certain columns in table A, some columns in table B be updated.
for eg if table A has column named 'salary' and table B has column named 'type'.
When I upload data in table A and then into B my column named 'type' in table B should be updated to rich,medium or poor depending on the value of salary in table A for employees which are commom in both table A and B.
Can anybody suggest me how can I acheive this ? since I want my 'type' column to be updated automatically instead of running a query afterwards and then updating the type status in table B.
Any help would be appreciated.
Thanks in advance.
Re: Updating columns of one table based on other table [message #346363 is a reply to message #346360] Mon, 08 September 2008 06:19 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

Try using Triggers for this.

Regards,
Jo
Re: Updating columns of one table based on other table [message #346367 is a reply to message #346363] Mon, 08 September 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use trigger, use a procedure.
Never do things by magic, if it is a business requirement, put it explicitly in code.

Regards
Michel

Re: Updating columns of one table based on other table [message #346372 is a reply to message #346363] Mon, 08 September 2008 06:39 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi jo,
Thanks for your reply.
I used and after insert trigger on table B since the column to be updated was from table B ,but got an error messaeg saying the trigger was mutating itself, did not undertand what it meant actually.
So I used an after update trigger and thought I did not get any error but nothing seemed to happen to my column status either i.e it was not updated.
Can you suggest me what went wrong.
Re: Updating columns of one table based on other table [message #346376 is a reply to message #346372] Mon, 08 September 2008 06:43 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
As suggested by Michel, Do avoid use of triggers if it is a business requirement



Regards,
Oli
Re: Updating columns of one table based on other table [message #346377 is a reply to message #346367] Mon, 08 September 2008 06:44 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Hi michel,
thanks for your reply,
I wish I could put my queries explicitly in a code.
I tried that and was able to do that.But the thing is that I want my column in table B to be updated automatically on its own after data upload in table B depending on the condition in table A for common records in both the tables.
As far as procedure is concerned ,can you explain in alittle detail how would I call the procedure after upoading data in table B.
Thanks
Re: Updating columns of one table based on other table [message #346378 is a reply to message #346360] Mon, 08 September 2008 06:47 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel,

joebunny wrote on Mon, 08 September 2008 16:34

since I want my 'type' column to be updated automatically instead of running a query afterwards and then updating the type status in table B.



Can a procedure be sufficient for this requirement?

Regards,
Jo
Re: Updating columns of one table based on other table [message #346381 is a reply to message #346377] Mon, 08 September 2008 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But the thing is that I want my column in table B to be updated automatically on its own after data upload in table B depending on the condition in table A for common records in both the tables.

Don't allow anyone to update the table but the procedure.

Quote:
As far as procedure is concerned ,can you explain in alittle detail how would I call the procedure after upoading data in table B.

There 2 issues: initial loading, everyday task.
Which one are you talinkg about?

Regards
Michel
Re: Updating columns of one table based on other table [message #346383 is a reply to message #346378] Mon, 08 September 2008 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joicejohn wrote on Mon, 08 September 2008 13:47
Michel,
...
Can a procedure be sufficient for this requirement?

See my previous answer: no more direct update is allowed.

Regards
Michel

Re: Updating columns of one table based on other table [message #346394 is a reply to message #346381] Mon, 08 September 2008 07:28 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Quote:

There 2 issues: initial loading, everyday task.
Which one are you talinkg about?


Its a once a month upload at the start of every month.
So you can say its quite a frequent task.

Quote:
Don't allow anyone to update the table but the procedure.

How can I acheive that.
Also I would like to mention that this is not a busin ess requirement but its for my own reference which I will be using for my further calculations in the application.
Re: Updating columns of one table based on other table [message #346403 is a reply to message #346394] Mon, 08 September 2008 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Also I would like to mention that this is not a busin ess requirement but its for my own reference which I will be using for my further calculations in the application.

If it is yours then don't store it in the table, calculate it each time and/or create a (m)view.

Regards
Michel
Re: Updating columns of one table based on other table [message #346412 is a reply to message #346403] Mon, 08 September 2008 08:28 Go to previous messageGo to next message
joebunny
Messages: 18
Registered: September 2008
Junior Member
Confused

But the number of records that I have is fairly large and I have about 5 to 6 columns in table to be updated depending on certain columns in table A.
Maybe the salary example was not the best example it was just a dummy example that I used to explain my scenario.
Is it possible through the use of triggers.?
If yes how..?
Thanks
Re: Updating columns of one table based on other table [message #346414 is a reply to message #346412] Mon, 08 September 2008 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then create a mview.

Regards
Michel
Re: Updating columns of one table based on other table [message #346422 is a reply to message #346412] Mon, 08 September 2008 09:13 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

For the mutating table error you are getting try to go through the following link.
http://asktom.oracle.com/tkyte/Mutate/index.html
But I suggest you take the suggestion given by Michel and create a materialized view to solve your issue.

Regards.
Jo
Re: Updating columns of one table based on other table [message #346636 is a reply to message #346422] Tue, 09 September 2008 03:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why not simply call the procedure from a Statement level After Insert trigger.
That way you get the benefit of explicitly defined business rules, plus you can guarantee that they get run at the right time.

create table test_0079 (col_1 number, col_2 number);

create or replace procedure test_0079_post_ins as
begin
  update test_0079
  set col_2 = sign(col_1);
end;
/

create or replace trigger test_0079_ais 
after insert on test_0079
begin
  test_0079_post_ins;
end;
/

insert into test_0079(col_1) select level -5 from dual connect by level <= 10;

select * from test_0079;

     COL_1      COL_2
---------- ----------
        -4         -1
        -3         -1
        -2         -1
        -1         -1
         0          0
         1          1
         2          1
         3          1
         4          1
         5          1

10 rows selected.
Re: Updating columns of one table based on other table [message #346651 is a reply to message #346367] Tue, 09 September 2008 04:26 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear michel

Quote:

if it is a business requirement, put it explicitly in code.



Is there any specific reason to avoid triggers when it is a business requirement.

yours
dr.s.raghunathan
Re: Updating columns of one table based on other table [message #346653 is a reply to message #346651] Tue, 09 September 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/t/124549/102589/

Regards
Michel
Re: Updating columns of one table based on other table [message #346655 is a reply to message #346653] Tue, 09 September 2008 04:34 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
thanks michel Shocked

yours
dr.s.raghunathan
Re: Updating columns of one table based on other table [message #346663 is a reply to message #346651] Tue, 09 September 2008 05:08 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
dr.s.raghunathan wrote on Tue, 09 September 2008 11:26
dear michel

Quote:

if it is a business requirement, put it explicitly in code.



Is there any specific reason to avoid triggers when it is a business requirement.

yours
dr.s.raghunathan


Triggers are also code.
Business requirements that are implemented by triggers are also explicitly put in code.
Previous Topic: PL/SQL Error
Next Topic: regarding external tables
Goto Forum:
  


Current Time: Sat Dec 03 11:52:17 CST 2016

Total time taken to generate the page: 0.08182 seconds