Update table1 from table2 (Insert new, update changed only, remove missing)

Question

I see similar question asked a dozen times, but none quite my scenario.

My intent is to have a current table (table1) updated based on csv imported temporary table (table2);

1. Remove missing appids
2. Add new appids
3. Update name if it has changed

Additionally there is a 2 triggers for after update (thanks to which I realized what I was doing and not simply running with broken code);

1. log updates in history table (table3)
2. log deletes in history table (table3)

Currently I perform operations as follows:

CREATE TEMPORARY TABLE table2 (
    appid INT,
    name VARCHAR(255)
    );
LOAD DATA LOCAL INFILE "outp.csv" INTO TABLE table2 
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';

UPDATE table1
INNER JOIN table2 ON table2.appid = table1.appid
SET table1.name = table2.name;

DROP TEMPORARY TABLE table2;

---

Currently history table gets every existing row whenever I run the update, plus new rows are not imported and missing rows are not removed.

What would be the correct way to accomplish the the intended update operation?

---

Edit: Actually, it made more sense to go with the exact statements provided by GMB below. After I realized how the insert statement works, it just makes more sense to avoid having to run a third unnecessary statement since insert will do both insert and update in one.

Old three statement script for reference:

DELETE FROM table1
WHERE NOT EXISTS(
    SELECT 1
    FROM table2
    WHERE table2.appid = table1.appid
    );

UPDATE table1 INNER JOIN table2 ON table2.appid = table1.appid
SET table1.name = table2.name
WHERE table1.name <> table2.name;

INSERT INTO table1 (appid, name)(
    SELECT table2.appid, table2.name FROM table2
    LEFT JOIN table1 ON table2.appid = table1.appid
    WHERE table1.appid IS NULL
    );

Answer 1

You can't do that in a single statement. Unlike some other databases, MySQL/MariaDB does not provide a fully-featured merge statement.

You can, however, perform the operations in two steps: first get rid of "missing" rows in the target, then use insert ... on duplicate key to insert/update the new and existing rows:

delete from table1 
where not exists(select 1 from table2 where table2.appid = table1.appid)

insert into table1 (appid, name)
select appid, name
from table2
on duplicate key update name = values(name)

This assumes, of course, that id is the primary key of table1 (so the insert statement can properly identify the duplicates). I would also recommend defining id as the primary key of temporary table table2: this wil make the queries faster.