asked on

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

Bill Prew

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

Yong Scott

ASKER

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

ASKER CERTIFIED SOLUTION

Tomas Helgi Johannsson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Yong Scott

ASKER

thank you , you help me alot :)