Insert/Update Data

[shadaddy]

When I insert/update data and change a field's value which was NULL, everything seems fine at first. When I go to look at the data again, all fields that were NULL now become 0 (ZERO). Is this supposed to happen? I can go back and press Alt-N and set all fields back to NULL, but this becomes time consuming when tables have 40 fields.

[Ritesh]

What is the the structure of the table you are updating the table. Please check the SQL which SQLyog generates in the history tab. Is it what you expect?

Many a times MySQL does internal conversion of data to make it correct.

[shadaddy]

Here is my output. The first is from setting a value to 0 and it changes all of the others to 0. The second output is when I press Alt-N on a field to set it to NULL.

[6:19:41 AM][ 10 ms] show databases

[8:45:36 AM][ 901 ms] show table status from `Potlines`

[8:45:54 AM][ 350 ms] select * from `Potlines`.`dailyreductionsummarygoalstable` limit 0,5000

[8:46:03 AM][ 0 ms] show fields from `Potlines`.`dailyreductionsummarygoalstable`

[8:46:03 AM][ 0 ms] show keys from `Potlines`.`dailyreductionsummarygoalstable`

[8:46:03 AM][ 10 ms] select * from `Potlines`.`dailyreductionsummarygoalstable` limit 0, 5000

[8:46:09 AM][ 0 ms] update `Potlines`.`dailyreductionsummarygoalstable` set `Date`='2003-12-01', `Name`='craig hall', `GrossAlLbs`='40000000', `MetalAdjust`='0', `NetAlLbs`='', `LbsPotDay`='', `ColdAdjust`='', `Rodding`='', `OperCells`='', `AvgAmps`='0', `AvgVolts`='0', `GrossVolts`='0', `KwCell`='0', `KwhLbAl`='0', `MinPwrOff`='0', `PerCurrEff`='0', `AdjPerCurrEff`='0', `AcDcEff`='0', `AvgTotalAE`='0', `AvgAEMin`='0', `AEPotDay`='0', `AvgMinPerAE`='0', `AvgPerAl`='0', `PerAl9985`='0', `PerAl9990`='0', `AvgPerFe`='0', `AvgPerSi`='0', `BathDepth`='0', `MetalDepth`='0', `Temperature`='0', `AvgPerExsAlF3`='0', `AvgPerSpar`='0', `AnodesReceived`='0', `GrossTonsC`='0', `TonsButts`='0', `LbsPerAnode`='0', `LbsPerButt`='0', `GrossCPerLbAl`='0', `NetCPerLbAl`='0', `TonsCry`='0', `TonsAlF3`='0', `TonsLithium`='0' where Date='2003-12-01'

[8:46:10 AM][ 10 ms] select * from `Potlines`.`dailyreductionsummarygoalstable` limit 0,5000

[8:46:38 AM][ 0 ms] show fields from `Potlines`.`dailyreductionsummarygoalstable`

[8:46:38 AM][ 0 ms] show keys from `Potlines`.`dailyreductionsummarygoalstable`

[8:46:38 AM][ 10 ms] select * from `Potlines`.`dailyreductionsummarygoalstable` limit 0, 5000

[8:46:45 AM][ 0 ms] update `Potlines`.`dailyreductionsummarygoalstable` set `Date`='2003-12-01', `Name`='craig hall', `GrossAlLbs`='40000000', `MetalAdjust`=NULL, `NetAlLbs`='0', `LbsPotDay`='0', `ColdAdjust`='0', `Rodding`='0', `OperCells`='0', `AvgAmps`='0', `AvgVolts`='0', `GrossVolts`='0', `KwCell`='0', `KwhLbAl`='0', `MinPwrOff`='0', `PerCurrEff`='0', `AdjPerCurrEff`='0', `AcDcEff`='0', `AvgTotalAE`='0', `AvgAEMin`='0', `AEPotDay`='0', `AvgMinPerAE`='0', `AvgPerAl`='0', `PerAl9985`='0', `PerAl9990`='0', `AvgPerFe`='0', `AvgPerSi`='0', `BathDepth`='0', `MetalDepth`='0', `Temperature`='0', `AvgPerExsAlF3`='0', `AvgPerSpar`='0', `AnodesReceived`='0', `GrossTonsC`='0', `TonsButts`='0', `LbsPerAnode`='0', `LbsPerButt`='0', `GrossCPerLbAl`='0', `NetCPerLbAl`='0', `TonsCry`='0', `TonsAlF3`='0', `TonsLithium`='0' where Date='2003-12-01'

[8:46:47 AM][ 0 ms] select * from `Potlines`.`dailyreductionsummarygoalstable` limit 0,5000

[Ritesh]

😮 Its difficult to tell anything from the query. Do you find any error in the query?

If so, then please send the table with its structure and data to [email protected] so that we can work on the problem.

Step by step operation to reproduce the error will be appreciated.

[Author]

Posts