put the output into new column in the table

I want put the output into new column in the table but error
INSERT INTO nov (ingb)
select disk_space_size,
       
           CAST(left(disk_space_size, locate(' ', disk_space_size)) AS DECIMAL(10,2))
           *
         CASE WHEN RIGHT(disk_space_size, 2) = 'GB' then 1.0
             when RIGHT(disk_space_size, 2) = 'TB' then 1024.0
             when RIGHT(disk_space_size, 2) = 'MB' then 1.0/1024
             else 1.0
         end
              
             inGB
from nov;

Open in new window


#1136 - Column count doesn't match value count at row 1
Scott Yong Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
You are only inserting into a single column ingb of the nov table.  But your query that generates the rows to insert is returning more than one column.  I'm not sure what value you were trying to insert into that column, but you have to return only that single column in the query.

Remove the disk_space_size column from the query, like this (not sure if the rest of the query is correct though...)

INSERT INTO nov (ingb)
select CAST(left(disk_space_size, locate(' ', disk_space_size)) AS DECIMAL(10,2))
           *
         CASE WHEN RIGHT(disk_space_size, 2) = 'GB' then 1.0
             when RIGHT(disk_space_size, 2) = 'TB' then 1024.0
             when RIGHT(disk_space_size, 2) = 'MB' then 1.0/1024
             else 1.0
         end AS inGB
from nov;

Open in new window


»bp
1
Scott Yong Author Commented:
dude when  i create new column and i insert that code .. its doesn't fill in the first row of the column then appear the value of null Capture.PNG
0
Tomas Helgi JohannssonCommented:
Hi!

I belive that what you want to do is do an update on existing rows to fill in missing data in ingb.
This should do what you are aiming for
update nov set inGB = CAST(left(disk_space_size, locate(' ', disk_space_size)) AS DECIMAL(10,2))
           *
         CASE WHEN RIGHT(disk_space_size, 2) = 'GB' then 1.0
             when RIGHT(disk_space_size, 2) = 'TB' then 1024.0
             when RIGHT(disk_space_size, 2) = 'MB' then 1.0/1024
             else 1.0
         end

Open in new window


Regards,
    Tomas Helgi
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott Yong Author Commented:
thank you , you help me alot :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.