update rows after left join

Question

I'm looking for a way to update some values in a table if they were used in a left join.

I have two tables:

table1:
id | name | age | job
1  | john | 31  | 
2  | eric | 25  | 

table2:
id | job | inserted
1  | lawyer | 0
2  | dentist | 1
3  | cop    |  0

Then I run the query:

UPDATE  table1
LEFT JOIN
        table2
ON      table1.id = table2.id
SET     table1.job = `table2.job`
WHERE   table2.inserted = 0

But I want to update the rows from table2 that were used in the update so they have inserted = 1. This for two reasons 1) to speed up the join and 2) so I can check which rows of table2 were not used. (The inserts in table2 happen before table1, but the ids in table2 should always be present in table1 if all cron jobs run okay.)

Answer 1

You shouldn't be using a LEFT JOIN, since you only want to update rows in table1 that have a matching row in table2. Try:

UPDATE table1 AS t1
JOIN table2 AS t2 ON t1.id = t2.id
SET t1.job = t2.job, t2.inserted = 1
WHERE t2.inserted = 0

DEMO