Lookup value on insert in MYSQL

When inserting data into a table I need to be able to populate the table with data from another table based on an entry in the new record.

For example I have a table called latitude that isn't populated when a new record is 开发者_如何学Pythoncreated.

Latitude details are stored in a table called area which has a column called 'areaid' and one called 'latitude'.

I need the latitude field in the area table to be updated with the correct latitude based on the areaid inserted in the new record.

I am sorry if this is confusing, any questions please getin touch.

Thanks

I assume here that areaid is a unique or primary key in the area table.

You can use either REPLACE INTO or INSERT ... ON DUPLICATE KEY UPDATE. If you are going to keep the latitude in the latitude table as well then you could use a trigger to ensure that the area table is updated when you insert into the latitude table.

CREATE TRIGGER example BEFORE INSERT ON latitude
  FOR EACH ROW BEGIN
    REPLACE INTO area (areaid, latitude) VALUES (NEW.areaid, NEW.latitude);
  END;