MySQL trigger with join

Question

I have two tables, a logins table which captures login information, and a ranges table which associates IP info with countries. I am trying to create a trigger which updates the logins.country column after an insert by performing a join to the ranges table.

The tables are structured as such:

CREATE TABLE logins (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
users_id int(10) unsigned NOT NULL,
ip int(10) unsigned NOT NULL,
country varchar(2) DEFAULT NULL,
status tinyint(4) NOT NULL,
timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE ranges (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
first int(10) unsigned NOT NULL,
last int(10) unsigned NOT NULL,
country varchar(2) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY first_UNIQUE (first),
UNIQUE KEY last_UNIQUE (last)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have this trigger but it doesn't seem to work:

CREATE TRIGGER update_country
AFTER INSERT ON logins
FOR EACH ROW
UPDATE logins l
JOIN ranges r ON l.ip >= first AND l.ip <= last
SET l.country = r.country
WHERE l.id = NEW.id;

Any idea where I'm going wrong here?

Answer 1

After playing around with this for a while, I realized a join wasn't necessary and I was making this far more complex than it needed to be. Here is the final trigger:

CREATE TRIGGER update_country BEFORE INSERT
ON logins FOR EACH ROW
SET NEW.country = (SELECT country FROM ranges WHERE NEW.ip >= first AND NEW.ip <= last);