PHP如何设置脚本以从两个字段中获取两个数字值,然后添加它们并更新到第三个字段

问题描述:

I have a table with various fields 2 of which contain a number between 1 and 50.

I need help setting up a script that will automatically take two set fields add them and insert the total into a third field for each indivdual record for all records in the table.

I'm stuck in creating this because some records will have 0 or NULL values.

Please help :)

I've think I may be able to do this purely in SQL but the closest i found was this:

Alter table Mytable add column Keywords varchar(255);
update Mytable set Keywords = concat(Categories, ' ', Tags);

Obviously I would not need to add the column etc. Either a PHP or SQL solution would be fantastic.

Example in case I'mm not explaining this well it's confusing me a lot

Column 1 - Field Value: 20
Colum 2 - Field Value 20
Add Colum 1 + Colum 2 = 40
Insert 40 into Colum 3

Thanks

我有一个包含各种字段的表2其中包含1到50之间的数字。 p>

我需要帮助设置一个脚本,该脚本将自动获取两个设置字段,并将总数插入到表格中所有记录的每个单独记录的第三个字段中。 p>

我坚持创建这个,因为有些记录会有0或NULL值。 p>

请帮助:) p>

我认为我可以纯粹在SQL中执行此操作,但我发现的最接近的是: p>

 更改表Mytable添加列关键字varchar(255); 
update Mytable set Keywords = concat(Categories,'',Tags); 
  code>  pre> \  n 
 

显然我不需要添加列等。无论是PHP还是SQL解决方案都会很棒。 p>

例如,我没有解释这个问题让我很困惑 p>

 第1列 - 字段值:20 
Colum 2  - 字段值20 
添加Colum 1 + Colum 2 = 40 
将40插入Colum 3 
  code>  pre> 
 
 

谢谢 p> div>

This should do it

UPDATE [TABLE_NAME] SET col3 = [TABLE_NAME].col1 + [TABLE_NAME].col2;

Replace [TABLE_NAME] and col1, col2, col3 with your tables and columns

UPDATE your_table SET Column3 = Column1+Column2

or

UPDATE your_table SET Column3 = IFNULL ( Column1, 0 ) + Column2

to avoid NULs on Column1

Try the following:

UPDATE Mytable SET column3 = column2 + column1

this should work for 0 , however if you have got NULL values in any of the adding column you will get a NULL in the result field, so in that case the query is slightly different

for e.g. if Column1 may have null values, then the query should be

UPDATE Mytable SET column3 = IFNULL(column1 ,0) + column2

and if both the fields can have NULL values then,

UPDATE Mytable SET column3 = IFNULL(column1 ,0) + IFNULL(column2 ,0)

You should do this in plain SQL.

If both columns are stored as numbers:

UPDATE tableName 
SET `Column 3` = IFNULL(`Column 1`,0) + IFNULL(`Column 2`,0)

If they are stored as characters:

UPDATE tableName
SET `Column 3` = CAST(IFNULL(`Column 1`,0) AS SIGNED)
               + CAST(IFNULL(`Column 2`,0) AS SIGNED)