CODEWITHSUNDEEP
mysql
RickyB
RickyB

Reputation: 985

mysql, create field based on existing data

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

Upvotes: 0

Views: 520

Answers (2)

Randy
Randy

Reputation: 16677

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

Upvotes: 0

Basic
Basic

Reputation: 26756

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

Upvotes: 4

Related Questions