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

Question

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?

Why are you doing a self-join in your update? Can you show us sample data from your table? — Tim Biegeleisen, Mar 08 '17 at 07:29
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 — , Mar 08 '17 at 07:36
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, Mar 08 '17 at 07:38

udhaya kumar · Answer 1 · 2017-03-08T07:35:53.827

2

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

edited Mar 08 '17 at 07:35

answered Mar 08 '17 at 07:29

udhaya kumar

161
6

3

This would make a great comment but a poor answer. Please edit it to add some content. – Zohar Peled Mar 08 '17 at 07:33
@zohar-peled Of course you're right, but to be fair: It does answer the only question asked :) – Onkel Toob Mar 08 '17 at 07:36

score 1 · Accepted Answer · answered Mar 08 '17 at 07:33

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

score 0 · Answer 3 · answered Mar 08 '17 at 07:41

0

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.

answered Mar 08 '17 at 07:41

ProgrammerBoy

876
6
19

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

3 Answers3