UPDATE table1
SET active = 0
WHERE id IN (
SELECT
table1.id
FROM table1 LEFT JOIN table2 ON
(table1.name <=> table2.name
AND table1.start <=> table2.start
AND table1.id <=> table2.id
AND table1.ord <=> table2.ord)
WHERE table2.id IS NULL)
Basically I am trying to find records in table1 but not in table2, and then set up these records "active=0".
If I am running the above query, I would get "You can't specify target table 'table1' for update in FROM clause".
The solution could be two steps
step1) get the id from
SELECT
table1.id
FROM table1 LEFT JOIN table2 ON
(table1.name <=> table2.name
AND table1.start <=> table2.start
AND table1.id <=> table2.id
AND table1.ord <=> table2.ord)
WHERE table2.id IS NULL
step 2) update table1 with the ids from step 1.
But is there a subquery solution to let me do these in one step?
I run into this kind problem before, but I changed the subquery to make it work in one step. But in this case, the subquery is as it is. So what will be the solution?
Thanks!
