ref is the auto increment and
ID is the customer identification number. Hence there are multiple
ID for each customer (which form a history of each customer). I want to update the most recent record for each customer by setting the
likelihood field to 1 in all cases - but without creating a new record.
I thought a query like this would do the job:
UPDATE customer SET likelihood = 1 WHERE customer.ref IN (SELECT MAX(ref) FROM customer GROUP BY ID)
but I get the following error:
"#1093 - You can't specify target table 'customer' for update in FROM clause"
Where am I going wrong?
Edit: Some sample data (not the complete table)
ref | ID | likelihood | name ----+----+------------+----- 1 | 1 | 0 | a 2 | 2 | 0 | b 3 | 1 | 0 | a 4 | 2 | 0 | b 5 | 1 | 0 | a 6 | 1 | 0 | a
So I want to pick out:
4 | 2 | 0 | b 6 | 1 | 0 | a
and change them to:
4 | 2 | 1 | b 6 | 1 | 1 | a
I cannot remember whether it will allow a self join in an update. Give this a try -
UPDATE customer c1
LEFT JOIN customer c2
ON c1.ID = c2.ID
AND c1.ref < c2.ref
SET c1.likelihood = 1
WHERE c2.ID IS NULL
EDIT I have changed the column used in the second join condition from ID to ref.
You are getting the error because you cannot update the table that you are selecting from. The where clause you are writing is selecting from the customers table, so you can't write to it in the update
You will probably need two queries to pull this off such as the following:
SELECT @max_ref:=max(ref) FROM customers;
UPDATE customers SET likelihood=1 WHERE [email protected]_ref