Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wderrick27
Starting Member
3 Posts |
 Posted - 2007-05-05 : 14:17:46
|
| I'm kind of new to SQL and would appreciate some help with this. I have a table named Company. In this table there are fields called CompanyID, CompanyName, Address, and Year. (There are other fields too, but they are not relevant for this post.) There are multiple companies in the table and each company has a unique ID number. However, each company can have up to three records in the table. (One for the year 2005, one for the year 2006, and one for the year 2007.) Currently, only a company's 2007 record has data for the address. It's 2005 and 2006 records have addresses that are blank. I want to be able to update the 2005 and 2006 records with address from the 2007 record. Obviously, a very simple update won't work because there are multiple companies and multiple records that need updating. Can anyone fill me in on the SQL statement(s) I would need to make this happen? I would be extremely appreciative. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-05 : 14:29:39
|
[code]update tset Addresss = f.Addressfrom Company f inner join Company ton f.CompanyID = t.CompanyIDand f.Year = 2007and t.Year <> 2007[/code]
KH |
 |
|
|
wderrick27
Starting Member
3 Posts |
Posted - 2007-05-05 : 15:22:13
|
| Thanks for the help. I didn't understand exactly what was going on in your statement, so I tried just copying and pasting it into my Access DB. However, I got a syntax error. It says "missing operator in query expression" and then lists the query from f.Address to the end of the query." (Seems it got upset once it hit the f.Address.) So, maybe you wouldn't mind explaining a little bit about what's going on in the SQL statement you posted. It looks like you're doing a self join on the table. My limited understanding of this is that it "kind of" creates two tables when you do a self join. I guess you're calling the two tables f and t? You don't need to explicitly declare the f and t in any way? The logic after the FROM line makes perfect sense to me. It's the FROM line and before that I'm a little fuzzy about. For whatever reason, it's not working at the moment. So, some more input and maybe an explanation of how it's intended to work would be great. Thanks again! |
|
|
|
wderrick27
Starting Member
3 Posts |
Posted - 2007-05-05 : 17:23:20
|
| Okay, I fooled around with it and this is what I came up with:UPDATE CompanyINNER JOIN Company AS Company_1ON Company.CompanyID = Company_1.CompanyIDSET Company.Address = Company_1.AddressWHERE Company_1.Year = 2007 AND Company.Year <> 2007;It worked. Anyone see any problems with this? Why is my statement so different than yours khtan? Will this statement only work with MSAccess? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-05 : 19:22:13
|
| The "New to SQL Server" forum is for questions about SQL Server, so khtan probably assumed that is what you are working with, and provided an answer in SQL Server syntax.If you are using Access, you should post your questions on the "MS Access" forum.CODO ERGO SUM |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-07 : 06:46:05
|
quote:
Originally posted by wderrick27
Okay, I fooled around with it and this is what I came up with:UPDATE CompanyINNER JOIN Company AS Company_1ON Company.CompanyID = Company_1.CompanyIDSET Company.Address = Company_1.AddressWHERE Company_1.Year = 2007 AND Company.Year <> 2007;It worked. Anyone see any problems with this? Why is my statement so different than yours khtan? Will this statement only work with MSAccess?
Yes thats the correct syntax in AccessSystax of Update with Inner join differs in SQL Server and AccessMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Copying fields in the same table