CODEWITHSUNDEEP
phpmysqlsqlsql-update
Braddles
Braddles

Reputation: 41

PHP How to setup a script to take two number values from two fields add them and update into 3rd field

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

Upvotes: 1

Views: 757

Answers (4)

Amitabh
Amitabh

Reputation: 739

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)

Upvotes: 2

bensiu
bensiu

Reputation: 25574

UPDATE your_table SET Column3 = Column1+Column2

or

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

to avoid NULs on Column1

Upvotes: 1

JohnP
JohnP

Reputation: 50019

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

Upvotes: 1

The Scrum Meister
The Scrum Meister

Reputation: 30111

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)

Upvotes: 2

Related Questions