Home » SQL & PL/SQL » SQL & PL/SQL » Update Table based on Separate Query
Update Table based on Separate Query [message #333197] Thu, 10 July 2008 16:02 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
I have 1 table and 1 Query



Table-1
Employeeid, Employeename


and Query-2
select distinct employeeid, employeename from employee-master


Update Table-1 SET EMPLOYEENAME = "||Query2.EMPLOYEE-||
ON Table1-EMPLOYEEID = Query1-EMPLOYEED


So basically I would like to select distinct list of employees, and use that selected to update the other table..

How is this done?

Re: Update Table based on Separate Query [message #333199 is a reply to message #333197] Thu, 10 July 2008 16:09 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Dash "-" characters are NOT valid within object names.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#sthref9987

Third Normal Form would eliminate/not allow this UPDATE.
Never store duplicated data.

[Updated on: Thu, 10 July 2008 16:18] by Moderator

Report message to a moderator

Re: Update Table based on Separate Query [message #333208 is a reply to message #333197] Thu, 10 July 2008 18:08 Go to previous messageGo to next message
gungalagunga
Messages: 3
Registered: July 2008
Junior Member
Don't you love those kind of answers? Third normal form . . . . hmmmmmmm, I was handed this database and it's not close to n3 . . . . . or we're merging two companies, both running 24x7 and the baseline was exported 20 days ago (and updates to the 30,000 users are done 100 times per day).

Anyway, write an anonymous block to do the job:

begin

for i in (
select
emp_id,
emp_name
from
master_table)

loop

update
other_table
set
emp_name = i.emp_name
where
matching_id = i.emp_id;

end loop;

commit;
end;
/

Re: Update Table based on Separate Query [message #333209 is a reply to message #333197] Thu, 10 July 2008 18:18 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> I was handed this database and it's not close to n3
The prime directive of holes, is that when you find yourself in a hole the 1st thing you should do is STOP DIGGING!

What is the justification for making a bad situation worse when supposedly you know better?

[Updated on: Thu, 10 July 2008 18:43] by Moderator

Report message to a moderator

Re: Update Table based on Separate Query [message #333212 is a reply to message #333208] Thu, 10 July 2008 19:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
I was handed this database and it's not close to n3 . . . . . or we're merging two companies, both running 24x7 and the baseline was exported 20 days ago (and updates to the 30,000 users are done 100 times per day).

Do you know the OP or the current situation that he is in? If not, then why assume his situation rather than try to help advise him/her on better practice?
Also, as a newbie on this forum, do you think it is a good idea to take an attitude with a poster as experienced as anacedent? (note, I have no problem with someone taking an opposing view to anyone, but doing it with a cr4ppy attitude irritates me)
Re: Update Table based on Separate Query [message #333439 is a reply to message #333197] Fri, 11 July 2008 11:49 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Yes. The table is in third normal form. The source for the update query is actually a selection of distinct records in the table itself.

Re: Update Table based on Separate Query [message #333443 is a reply to message #333208] Fri, 11 July 2008 11:56 Go to previous message
hdogg
Messages: 93
Registered: March 2007
Member
thanks gungalaguna!

I didnt even know about anonymous blocks.

I went a ahead and did some searching and found

a link for anonymous blocks

http://www.orafaq.com/wiki/Anonymous_block

Thanks everyone for the healthy discussion about hole digging and 3n.
Previous Topic: using utl_SMPT.VRFY
Next Topic: ORA-00603: ORACLE server session terminated by fatal error
Goto Forum:
  


Current Time: Sun Dec 04 14:37:51 CST 2016

Total time taken to generate the page: 0.17869 seconds