MySQL Trigger with Update

Question

I'm trying to update a row when it gets updated to keep one of the columns consistant,

CREATE TRIGGER user_country BEFORE UPDATE ON user_billing FOR EACH ROW
BEGIN
    IF NEW.billing_country = OLD.billing_country AND NEW.country_id != OLD.country_id THEN
     SET NEW.billing_country = cms.country.country_name WHERE cms.country.country_id = NEW.country_id;
    END IF;
END

But I keep recieving error #1064, Is there a way to update a row based on another row's data when the row is getting updated?

Answer 1

I don't use MySQL but does

SET NEW.billing_country =

Need to be

select NEW.billing_country =

Answer 2

I believe your "SET" line is missing a couple of things and should read something like this:

SET NEW.billing_country = (SELECT country_name FROM cms.country WHERE cms.country.country_id = NEW.country_id);