Mysql auto insert a value

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 开发者_JAVA百科somewhere. But is it possible to do this?

---

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

---

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.