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
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)