UPDATE question - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE question

Author

Topic

deronrb
Starting Member

6 Posts

Posted - 2005-10-24 : 09:36:46

I have one table with company name listings. I had another table that I used to append a few thousand records to the original company table. There was a problem with the phone numbers, I fixed them in the original Excel file, and I'm trying to UPDATE my company list with the new phone numbers, and it's not working:


UPDATE    tblCompanies
SET       ContactPhone =
  (SELECT     [PHONE NUMBER]
   FROM          WorkingFile w
   WHERE      tblCompanies.Company = w.[Company Name] AND tblCompanies.ContactFirstName = w.[FIRST NAME] AND tblCompanies.ContactLastName = w.[LAST NAME])

The WorkingFile table does not have the Autogenerated ID from the Companies table on it, so I'm trying to match it up by Company name and contact name. Am I having a problem because not all of the records on tblCompanies will have a match on WorkingFile?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-24 : 09:41:31

See if this helps you
http://sqlteam.com/forums/topic.asp?TOPIC_ID=56558

Madhivanan

Failing to plan is Planning to fail

deronrb
Starting Member

6 Posts

Posted - 2005-10-24 : 11:21:48

Thanks for that reference, it seems to be working... HOWEVER, i'm having a problem w/ data type conversion. I have a field of phone numbers coming from an excel file. it's getting brought into a SQL Server table as a float. I tried converting in the table design to nvarchar and it came in scientific notation. I then tried writing a conversion into the SQL and it still came in scientific notation. I searched the forum, found someone with the same problem who eventually solved it by first converting from float to int then to nvarchar, but my table won't allow me to convert from float to int, it's giving me a mathematical overflow error. :( Any help?

Thanks for this so far:


UPDATE    tblCompanies
SET              tblCompanies.ContactPhone = (CASE WHEN WF.[PHONE NUMBER2] = '' OR
                      WF.[PHONE NUMBER2] IS NULL THEN tblCompanies.ContactPhone ELSE WF.[PHONE NUMBER2] END)
FROM         tblCompanies LEFT JOIN
                          (SELECT     CAST([PHONE NUMBER] AS nvarchar(50)) AS [PHONE NUMBER2], [Company Name], [First Name], [Last Name]
                            FROM          WorkingFile) AS WF ON tblCompanies.Company = WF.[Company Name] AND tblCompanies.ContactFirstName = WF.[FIRST NAME] AND 
                      tblCompanies.ContactLastName = WF.[LAST NAME]

deronrb
Starting Member

6 Posts

Posted - 2005-10-24 : 12:23:05

Ah, got it working! Thanks SO much for the UPDATE help. My last step was going float to bigint to nvarchar. worked like a charm!


UPDATE tblCompanies
SET tblCompanies.ContactPhone = (CASE WHEN WF.[PHONE NUMBER2] = '' OR WF.[PHONE NUMBER2] IS NULL THEN tblCompanies.ContactPhone ELSE WF.[PHONE NUMBER2] END)
FROM tblCompanies LEFT JOIN (SELECT CAST(CAST([PHONE NUMBER] AS bigint) AS nvarchar) AS [PHONE NUMBER2], [Company Name], [First Name], [Last Name]FROM WorkingFile) AS WF 
ON tblCompanies.Company = WF.[Company Name] AND tblCompanies.ContactFirstName = WF.[FIRST NAME] AND tblCompanies.ContactLastName = WF.[LAST NAME]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 01:47:20

Why do you use nvarchar?
Isnt varchar enough?

Madhivanan

Failing to plan is Planning to fail

deronrb
Starting Member

6 Posts

Posted - 2005-10-25 : 21:14:04

varchar would work. i inherited this mess and it's my job to clean it up. :) thanks again for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-26 : 10:33:01

Read in BOL, Books On Line, SQL Server Help file for the major differences of datatypes

Madhivanan

Failing to plan is Planning to fail