Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I update a field in one table from a field in another table in orabcle

Posted on 2009-04-29
6
Medium Priority
?
176 Views
Last Modified: 2012-05-06
I have taken over a legacy system that now requires addresses to have the county name stored with them.  I have obtained a database with the city, stateabbreviation, county and postalcode in them, Imported the data into oracle 10g with the table name DEMOGRAPHICS
Now I need to update my Address table to fill in the county field
where the City, StateAbbreviation, PostalCode fields in the DEMOGRAPHICS = the City, StateAbbreviation, PostalCode fields in the Address Table.  Filling in the County field in the Address table with the County field in the DEMOGRAPHICS table
0
Comment
Question by:marktoth
  • 3
  • 3
6 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 24263321

update address
set county = d.county
from address a
inner join demographics d on a.city = d.city and a.stateabbreviation = d.stateabbreviation and a.postalcode = d.postalcode

Open in new window

0
 
LVL 1

Author Comment

by:marktoth
ID: 24263398
thanks but I get the following error when I run the statement

Sql Command not properly ended, Line 2 Column 21
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 24263505
Sorry like this:
update address a
set a.county = (
  select d.country 
  from demographics d 
  where a.city = d.city 
    and a.stateabbreviation = d.stateabbreviation 
    and a.postalcode = d.postalcode
)

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 61

Expert Comment

by:Kevin Cross
ID: 24263510
And d.country should be d.county...
0
 
LVL 1

Author Closing Comment

by:marktoth
ID: 31576067
Thanks!
0
 
LVL 1

Author Comment

by:marktoth
ID: 24264035
That saved me from having to look at about 102,000 records in our database!  Thanks!!!!!!!!!!!!!!!!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question