UPDATE statement with select count from the same table to find first time occurrence

I have a table of memberships and I would like to flag a given membership as “Firs Time” if there is not other membership before it for the same user.

I’m trying the blow in MySQL but I’m getting: “General error: 1093 You can’t specify target table ‘orgUserPlan’ for update in FROM clause”

UPDATE orgUserPlan SET isFirstTime = 
!(SELECT COUNT(id)FROM orgUserPlan
WHERE orgUserPlan.orgUser_id = 1 
AND orgUserPlan.id  < 123456
AND orgUserPlan.isDeleted != true
AND orgUserPlan.isCanceled != true) 
WHERE orgUserPlan.id = 123456

Answer

Right, MySQL doesn’t allow you to do a SELECT in an subquery from the same table that you’re already doing an UPDATE or DELETE from in the outer query.

MySQL does support multi-table UPDATE syntax. Your case could be done without using COUNT(*). Use a join instead.

UPDATE orgUserPlan AS o1
LEFT OUTER JOIN orgUserPlan AS o2
  ON o1.orgUser_id = o2.orgUser_id AND o1.id > o2.id
  AND o2.isDeleted != true AND o2.isCanceled != true
SET o1.isFirstTime = o2.id IS NULL;

If there is no other row with the same user id and an earlier id, then the outer join will return all columns of o2.* NULL. So that means o1 points to the earliest row for that user id.