October 18, 2011 at 6:10 pm
I am trying to update a table (TRANSACTIONS) based on the matching rows in a view (DONORS).
DONORS
TRANSACTIONNUMBER (Int(4) not null)
LETTERCODE (char(15), not null)
TRANSACTIONS
TRANSACTIONNUMBER (PK, Int(4) not null)
LETTERCODE (char(15), not null)
This is my update query:
update t
set t.LETTERCODE='UWOnly'
from db1.dbo.transactions as t
join db2.dbo.donors as d
on t.TRANSACTIONNUMBER=d.TRANSACTIONNUMBER
I get the following error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'OOA181 ' to data type int.
If I run the statement over and over, the what looks to be hexadecimal value changes. I do not have any hex in my data. I tried using ltrim and rtrim and checked the collation on the two databases and columns and am stumped. Any help would be appreciated!
October 18, 2011 at 6:38 pm
First of all your table definitions contained a few errors and I corrected same as shown below. I then inserted one row of data into each table.
CREATE TABLE #DONORS(
TRANSACTIONNUMBER Int not null
,LETTERCODE char(15) not null)
CREATE TABLE #TRANSACTIONS(
TRANSACTIONNUMBER Int not null
,LETTERCODE char(15) not null)
INSERT INTO #DONORS
SELECT 1,'XY'
INSERT INTO #TRANSACTIONS
SELECT 1,'NOT UWONLY'
--This is my update query:
update t
set t.LETTERCODE='UWOnly'
from #transactions as t
join #donors as d
on t.TRANSACTIONNUMBER=d.TRANSACTIONNUMBER
SELECT * FROM #TRANSACTIONS
I ran your statement as above. No error is generated, and the transactions column is updated.
October 19, 2011 at 11:31 am
Thank you for your reply ron. I am not querying against two tables, one is a view and one is a table. I think this is the problem.
-Danny
October 19, 2011 at 11:35 am
Based on what you are showing here, TransactionNumber in one of the objects has to be VARCHAR and has non-integer data in it.
Or since you mention a view, there has to be something in the view where there has to be a conversion to integer that is failing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2011 at 11:47 am
No, I dont think so because the following query works.
select *
from db1.dbo.transactions as t
join db2.dbo.donors as d
on t.TRANSACTIONNUMBER=d.TRANSACTIONNUMBER
So it seems like it is something with the set operator. But I am sure that the lettercode field is a char(15). I am checking this by right clicking the column and going to properties. It also says char(15) next to it in object explorer.
Thanks!
October 19, 2011 at 12:06 pm
Can you post the definition of the view?
Clearly, if the table definitions you have posted are correct the issue has to be in the view.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2011 at 12:31 pm
I am almost sure this has to do with the fact that I am updating from a view. I copied the results of the view qeury to excel and reuploaded it and matched the data types to that of the view and the update query worked. Oddly, it reported only updating 149 rows, but 151 rows were updated...
The view is formed from 2 other views. Could this be the issue? I also checked that the two views the Donors view queries has the same data types for the transactionnumber and lettercode fields using the method I mentioned before.
October 19, 2011 at 1:25 pm
So the issue had something to do with the fact that the view was pulling data from sql2005 and the table had data stored in a 2008 db. When I ran everything in the 2005 environment, it worked fine.
Thank you everyone for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply