MySQL: How to update records with subselect in where clause
Posted on 2010-08-24
I have migrated my application from MSSQL to MySQL and this is the last query that I can't figure out. I have a join table called ContactOwner which allows many AppUsers (AppUserOwnerId) to be associated with many Contacts (ContactId). In simple terms, it allows the application to know what user(s) own a particular Contact. I would like to delete all the rows for an AppUser where he is a "co-owner" with another specific user. The Query below did the trick in MSSQL, but MySQL objects to the sub-select near the "in" clause.
-- Deletes all rows where appUserId1 co-owns a contact with AppUserId2 (for appUserId1 only - appUserId2's rown still remain)
Delete From ContactOwner
where AppUserIdOwner = 1 and
ContactId in (select contactId from ContactOwner where AppUserIdOwner = 2)
Col 1: ContactId
Col 2: AppUserIdOwner
Is there another way to skin this cat? I would prefer the solution to still be a single SQL statement.