UPDATE after SELECTing certain records in MySQL

In my customer table, 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

-------------Problems Reply------------

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

Category:mysql Views:0 Time:2012-03-13
Tags: mysql

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.192 (s). 11 q(s)