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