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 id
s in table2
should always be present in table1
if all cron jobs run okay.)
You shouldn't be using a
LEFT JOIN
, since you only want to update rows intable1
that have a matching row intable2
. Try:DEMO