0

I have been working on a Query that takes the subset of the table records and update them but somehow i am unable to make it work it give me error #1093 - You can't specify target table 'members' for update in FROM clause

Here is the Query:

UPDATE members set is_deleted = 1 
   WHERE EXISTS 
     (SELECT m1.member_id FROM members as m1 
          WHERE m1.member_id NOT IN 
              (select member_id 
                   from membership_payment_profiles 
                   WHERE (next_date_to_charge > "2012-12-31 00:00:00" 
                   or next_date_to_charge is null)
          order by next_date_to_charge DESC
)
and m1.joined_on < "2012-12-31 00:00:00"
)
Yaser Nadeem
  • 21
  • 1
  • 6

1 Answers1

2

Try this query

update members m1 set m1.is_deleted=1 where m1.member_id not in (select distinct member_id 
                   from membership_payment_profiles 
                   WHERE (next_date_to_charge > "2012-12-31 00:00:00" 
                   or next_date_to_charge is null) order by next_date_to_charge DESC) and m1.joined_on < "2012-12-31 00:00:00";
Karunakar
  • 2,209
  • 4
  • 15
  • 20