Event Update Query Help

[Dale Turley]

Hi,

I hope there is somebody who can help - this is probably more of a logic problem, but because I'm using this in an event I placed it here.

I am creating an event on a MySql server and I am trying to create an update, which I'm struggling with.

I have two tables like these:

CREATE TABLE closed_headers
(
id BIGINT NOT NULL,
first INT,
second INT,
third INT
);

INSERT INTO closed_headers (id, first, second, third) VALUES
(7, 200, 150, 100),
(8, 40, 20, 5),
(9, 150, 100, 50);

CREATE TABLE headers_sub
(
id BIGINT,
user_id BIGINT,
vals INT
);
INSERT INTO headers_sub (id, user_id, vals) VALUES
(7, 1, 100),
(7, 2, 95),
(7, 3, 90),
(8, 2, 50),
(8, 5, 40),
(8, 1, 30);

What I need to do is: update a third table and add the first, second, and third values depending on the order of headers_sub.vals, so in headers_sub, the row 7, 1, 100 should match with first (the value 200) - because 100 is the highest value for that id and 7, 2, 95 should match with second (the value 150).

Does anybody know how I can do this? I've tried running loops in my XAMPP console and am getting nowhere at the moment and I'm considering using something like python, but I'd really like to keep this in MySql.

Thanks in advance.