Quote
INSERT INTO tmp_table
SELECT ID FROM TABLE3
UPDATE i, t, s
SET
t.name1 = s.name1,
t.name2 = s.name2,
t.name3 = s.name3,
t.name4 = s.name4
WHERE
i.id = t.i_id AND i.id in (SELECT ID from tmp_table);
First, you can't Insert into one table and Update another in the same statement. The command as shown could not have taken any time at all---it would exit with an error right away. You need a semicolon before the Update command, to make two commands.
Next, your Update comma join of i,t,s has no Where clause for table s, so it cross-joins s to the join of i and t. Surely not what you had in mind.
And, ...i.id in (SELECT ID from tmp_table)... will perform poorly, because MySQL optimises In(Select...) badly; that should become a simple inner join.
And as your errors show, comma joins look easier to write but are much harder to analyse and debug. Use explicit join syntax.
So your update query needs to be something like ...
UPDATE i
join t on i.id = t.i_id
join s on i.id = s.i_id -- or whatever the table s joining col is
join tmp_table on i.id=tmp_table.id
SET t.name1 = s.name1,
t.name2 = s.name2,
t.name3 = s.name3,
t.name4 = s.name4 ;