how insert data from one column to another sql server

Question

I'm having trouble inserting data from one column to another within the same table. I have no problem inserting individual rows but that is not what I want. I have a column called "PercentFull", I am multiplying this value by .01 to get the decimal equivalent, which is what I want to be inserted into a column I've called "NewPercentFull". I have tried the following:

insert into Table1 (NewPercentFull)
select PercentFull * .01
from Table 1

There is already data that populates the PercentFull column. However, after this query is executed, I get NULL for every column in my table, including the NewPercentFull. Anyone has an idea of how to achieve this? Thanks

Curtis · Accepted Answer · 2012-04-02 15:18:06Z

5

Inserting data from one column, into another, from the same record, would involve using an UPDATE statement:

UPDATE Table1
SET NewPercentFull=PercentFull * .01

With your query, you should've seen double the amount of records in Table1 after execution of that query.

You've mentioned that the values were NULL even for NewPercentFull. If this is the case, then I think my query may return NULL values too. This will occur when PercentFull is also NULL.

To avoid this, only UPDATE records which don't have a NULL value for PercentFull:

UPDATE Table1
SET NewPercentFull=PercentFull * .01
WHERE PercentFull IS NOT NULL

answered Apr 2, 2012 at 15:18

Curtis

104k69 gold badges280 silver badges359 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rick Over a year ago

thanks for your quick response. Yes, you're right, after I executed that query, I did have double the amount of entries. The query you have suggested seems to be on the right track, the problem now is that they are populated with "0" instead of the actual value. I find this odd since I know the "percentfull * .01" function works when i use a select as statement.

Curtis Over a year ago

@Rick it sounds like a rounding issue. What data types are your columns?

Rick Over a year ago

I have declared both, PercentFull and NewPercentFull as int. The NewPercentFull is a new column I created so this one is empty (I dont know if that will make a difference with the update statment)

Curtis Over a year ago

@Rick int is for integer, which are only whole numbers (hence the rounding). Try changing it to decimal(18,3)

Rick Over a year ago

I see the logic here. I have changed both data types to decimal, but now my NewPercentFull is being populated by 1's and 0's. I'm confused as to why this isn't working as it is when I use the select as statement.

|

Collectives™ on Stack Overflow

how insert data from one column to another sql server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related