Update sql statement from select on same table with sql server is not giving the right data

0
votes

So earlier I had a question about adding a 0 to the front of a field when it was 4 digits instead of 5.

The whole point was for me to end up with a select statement to update into the table, into another columns

concatenate a zero onto sql server select value shows 4 digits still and not 5

Code that I wrote that I hoped would work, but It ended up just instead doing it is inserting the same data

fullzip is want the new zip code should look like 

SELECT TOP 1000 [ZIP],
   right( '00000'+convert( varchar(5),ZIP) , 5) as fullzip
  ,[ZIP_Name]
FROM [CC].[dbo].[ServiceableZipCodes]

result of select 

Zip  fullZip   ZIP_Name
2030   02030    Dover

However, my update statement that I created looks like this and it did NOT add the zero :/ 

  UPDATE t1
  SET t1.ZipCode = right( '00000'+convert( varchar(5),t2.ZIP) , 5)
  FROM ServiceableZipCodes t1 INNER JOIN ServiceableZipCodes t2 on 
  t1.Zip = t2.zip
  WHERE t1.ZIP_Name = t2.ZIP_NAME

Now the new column of ZipCode that i inserted into from the update , it is only 4 characters

 DataTypes
 ZipCode   int
 ZIP  float

So it looks like this

   ZipCode  Zip   ZIP_Name
   2030       2030    Dover

What am I doing wrong?

3
sqlsql-server
Why are you doing a self-join in your update? Can you show us sample data from your table? - Tim Biegeleisen
I just want to update the same time with the correct zip code , which if it is 4 numbers, then I want to add zero to the front - user6321478
An int column will never "store" leading zeroes. If you want a numeric string that can contain arbitrary digits in any positions and on which you do not intend to perform any mathematical operations, use a varchar. - Damien_The_Unbeliever

3 Answers

2
votes
Integer datatype should not allow zero in first place
as well as float datatype

for example :

DECLARE @Rate FLOAT
SET @Rate=0125
SELECT @Rate

Output :
125
1
votes

Create a new column as varchar(10) ( in case you have carrier route ) You are trying to insert into a column with integer ... not going to work

you want a varchar

So example

    ZipCodeFull varchar(10)

Notice below your update statement sql with new column ZipCodeFull

     UPDATE t1
     SET t1.ZipCodeFull = right( '00000'+convert( varchar(5),t2.ZIP) , 5)
     FROM ServiceableZipCodes t1 INNER JOIN ServiceableZipCodes t2 on 
     t1.Zip = t2.zip
     WHERE t1.ZIP_Name = t2.ZIP_NAME

You should get the right results now

0
votes

If ZipCode column in your table is of type int then it will remove all the leading zeros. I think this is the case here.

SQLSERVER will not treat '02030' as a number. Better use varchar type for ZipCode column.

Hope this helps.