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
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.