Mysql auto insert a value

Question

Is it possible to get mysql to insert a value in a column based on whats being inserted into another column in the same table. eg: if i were to insert New York in one column, the other column could automatically be NY. Yes i'll have to store this info somewhere. But is it possible to do this?

Answer 1

Of course. You can use MySQL triggers.

EDIT: But for same table unfortunately, triggers are not allowed to re-open the table that they are associated with, or any tables that may be open at the time by that thread.

Answer 2

If I understood everything correctly, you're trying to insert a value in a column (New York), and another value in a second column which is dependent on the first value (in this case NY).

Let's use your city - state example and suppose you have a table cities with all the city - state associations.

INSERT INTO table SET
city = 'New York',
state = (SELECT state FROM cities WHERE city = 'New York');

You have to use this type of subquery, instead of using a trigger which will automatically fill the state column, as triggers in MySQL can't work on already opened tables, as @Michal Drozd pointed out. (They can, however, in SQL Server and Oracle at least).