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 |
|
sherrys
Starting Member
37 Posts |
 Posted - 2004-11-17 : 16:39:23
|
| I have a table that I am trying to build that already contains a unique family id. This table will hold family demographics.I am reading conversion data for individuals where I have already assigned the clients into families.How do I assign a phone number in the family demographics when I have multiple clients making up a family in my conversion data. I know that it is possible that the individual clients may in fact have different phone numbers in the conversion data, but I don't care. I will settle for the first match of the family id. Here is what I have tried. UPDATE tbl_wic_benefitfam SET BFam_HomePhoneAreaCd=(SELECT TOP 1 left(PHONE,3) from ndworking.dbo.client_working A WHERE A.bfam_id =bfam_Id)This gives everybody the same area code.I actually have several data elements that I want to grab from this record, but I keep paring down my query in hopes of any success. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-17 : 19:06:07
|
| The UPDATE is not written correctly, the tables are not correlated. You don't even need a TOP 1 to update this; in fact, without an ORDER BY clause TOP 1 is meaningless.Since you don't care which number you use, the following should work fine:UPDATE A SET BFam_HomePhoneAreaCd=LEFT(B.Phone, 3)FROM tbl_wic_benefitfam AINNER JOIN ndworking.dbo.client_working B ON A.bfam_id = B.bfam_IdWhen updating a table that is joined to another, you must alias the tables (A & B) and use the alias of the table you want to UPDATE (A) instead of the table name. |
 |
|
|
|
|
|
Update query with top 1 subselect