• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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

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
marktoth
Asked:
marktoth
  • 3
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:

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
 
marktothAuthor Commented:
thanks but I get the following error when I run the statement

Sql Command not properly ended, Line 2 Column 21
0
 
Kevin CrossChief Technology OfficerCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
And d.country should be d.county...
0
 
marktothAuthor Commented:
Thanks!
0
 
marktothAuthor Commented:
That saved me from having to look at about 102,000 records in our database!  Thanks!!!!!!!!!!!!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now