mysql, create field based on existing data

Question

I want to add a field if possible that contains data from another field on the same table. In this case I want everythhing in table.newfield to show values from table.oldfield that are less than 100. Can I do this in sql? Or shall I stick to doing this in a select query for reports?

Mysql 5,1 innodb strict mode

Answer 1

You could perform an update once you've added the new column eg

ALTER the table to add a Nullable Int/Long/??? called NewField

UPDATE Table SET NewField = OldField WHERE OldField<100

this woul, of course, only update when the query is run

You may also want to look into using views - these are effectively queries that are treated as tables. so you could have a view which does:

SELECT <Other Fields>,
    CASE WHEN OldField<100 THEN OldField
    ELSE NUll
END CASE
FROM table

(The syntax for the above is unchecked but should be approximately correct)

For more reading: Creating Views, MySQL Case Statements

Answer 2

if all you want is to display the short string, then do it all in SQL - do not store that data redundantly.

if you must store it again, then Basiclife got it right..