Home » SQL & PL/SQL » SQL & PL/SQL » Need help on Update SQL... (Oracle 9i)
Need help on Update SQL... [message #387280] Wed, 18 February 2009 13:38 Go to next message
preethi6945
Messages: 1
Registered: February 2009
Junior Member
Hi,

I am retrieving records which have more than 1 record for the same department.

select dept_name
from dept
group by dept_name
having count(*) > 1

Dept_Name Name State Eff_Dt
-----------------------------------
Accounts Smith MO 1/2/2009
Accounts John CA 2/2/2009
Finance Jim PA 3/5/2008
Finance Mathew FL 2/8/2007
Finance Jack FL 2/9/2005

I need to write an update statement to update values of Name and State for all the records with values from the record having maximum effective date.

My output should be like this.

Dept_Name Name State Eff_Dt
-----------------------------------
Accounts John CA 1/2/2009
Accounts John CA 2/2/2009
Finance Jim PA 3/5/2008
Finance Jim PA 2/8/2007
Finance Jim PA 2/9/2005

Can some one help mw how to achieve this with a single update statement?

Thanks
Preethi
Re: Need help on Update SQL... [message #387283 is a reply to message #387280] Wed, 18 February 2009 13:51 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a 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 tried so far.

Regards
Michel
Previous Topic: Result of quesry
Next Topic: After Update Trigger
Goto Forum:
  


Current Time: Thu Dec 08 20:11:18 CST 2016

Total time taken to generate the page: 0.10541 seconds