Update table.column with another table.column with common joined column

Question

Hit a speed bump, trying to update some column values in my table from another table.

This is what is supposed to happen when everything works

1. Correct all the city, state entries in tblWADonations by creating an update statement that moves the zip city from the joined city/state zip field to the tblWADonations city state

TBL NAME | COLUMN NAMES

1. tblZipcodes with zip,city,State
2. tblWADonations with zip,oldcity,oldstate

This is what I have so far:

 UPDATE    tblWADonations
 SET              oldCity = tblZipCodes.city, oldState = tblZipCodes.state
 FROM         tblWADonations INNER JOIN
                  tblZipCodes ON tblWADonations.zip = tblZipCodes.zip
 Where oldCity <> tblZipcodes.city;

There seems to be easy ways to do this online but I am overlooking something. Tried this by hand and in editor this is what it kicks back.

Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated. The statement has been terminated.

Please include a sql statement or where I need to make the edit so I can mark this post as a reference in my favorites. Thanks!

Answer 1

Take a look at the column definitions for both tables, in particular compare the columns you are trying to copy to each other - this should give you a hint as to why the error is cropping up.

As this is marked homework, I will leave my answer at that.

Answer 2

Check your table definitions to ensure that the maximum size of the VARCHAR fields you are trying to update match up. The error you are seeing indicates that the size of the field being updated/compared is smaller than the data you are trying to update it with.

As a rule of thumb, try and make sure than any similar fields (i.e. city and oldCity) are identically sized to avoid issues like this in the future.