asked on

MySQL: How to update records with subselect in where clause

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)

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

HainKurt

try this

Delete From ContactOwner
where AppUserIdOwner = 1 and
ContactId in (select c.contactId from ContactOwner c where c.AppUserIdOwner = 2)

burtonrhodes

ASKER

Still get the same error:

Error Code: 1093
You can't specify target table 'ContactOwner' for update in FROM clause

burtonrhodes

ASKER

A "select" statement works just fine. It's when I add the delete or update to the query. For example, below works fine:

select * from ContactOwner
where AppUserIdOwner = 1 and
ContactId in (select c.contactId from ContactOwner c where c.AppUserIdOwner = 2)

HainKurt

what about this

Delete From ContactOwner co
where co.AppUserIdOwner = 1 and
exists (select 1 from ContactOwner c where c.AppUserIdOwner = 2 and c.contactid=co.contactid)

burtonrhodes

ASKER

Same thing. I don't think it's a "naming convention thing". I think MySQL simply rejects the idea of a subselect when you are updating the same table in the delete clause.

burtonrhodes

ASKER

Just not sure how to solve this one in MySQL.

HainKurt

what about this trick

create v_authors_owner2 as
select ContactID from ContactOwner where c.AppUserIdOwner = 2

and use

Delete From ContactOwner co
where co.AppUserIdOwner = 1 and ContactID in (select contactid from v_authors_owner2)

burtonrhodes

ASKER

Not trying to make your job incredibly difficult, but I don't have the access to the database in order to "create" a view.

HainKurt

or use this

create temporary table temp as
select ContactID from ContactOwner where c.AppUserIdOwner = 2

Delete From ContactOwner co
where co.AppUserIdOwner = 1 and ContactID in (select contactid from temp)

HainKurt

from this sample

1 2
1 3
1 1
2 3
2 4
3 1
3 2

you want to delete 3rd row (1,1) and 6th row (3,1) correct?

HainKurt

what about this

update ContactOwner c
set AppUserIdOwner =-1
where AppUserIdOwner=1 and exists (select 1 from ContactOwner c2 where c.contactid=c2.contactid and c2.AppUserIdOwner != 1)

then a simple delete

Delete From ContactOwner co
where co.AppUserIdOwner = -1

burtonrhodes

ASKER

yep.

burtonrhodes

ASKER

MySQL still objects to the "update" in your last suggestion. For example, this won't work either:

Update ContactOwner
Set AppUserIdOwner = -1
where AppUserIdOwner = 1 and
ContactId in (select contactId from ContactOwner where AppUserIdOwner = 2)

burtonrhodes

ASKER

This is a bit of a bugger.

HainKurt

what about this? 33517585

create a temp table, use it in your delete query!

I guess this is the only way to do that

burtonrhodes

ASKER

Yeah - I will have to check if I have temp table privledges. I will get back to you that one!

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

mwvisa1,

The issue is, MySQL does not allow any select from an updated/deleted table ;)

Kevin Cross

I know. Using a JOIN gets around that if I remember correctly. The update query error is a big pain in the tail. I would search my question history to verify, but lost access to easy way ... but I may go about it the hard way to find for sure ... but know I have gotten around it before. :)

Kevin Cross

Found one instance -- it used derived table and JOIN, so can try it like that also.
https://www.experts-exchange.com/questions/24817097/sub-query-delete.html

Kevin Cross

If what you want is an actual update, then again use a JOIN; however, note with MySQL this goes in the UPDATE portion of the statement.

(just verified that JOIN syntax definitely works to fix the MySQL delete with subquery issue by the way -- so please try it -- note you may have to turn *safe* updates off in MySQL Workbench unless you go with the approach to change AppUserIdOwner = -1 and then you can delete with a simpleWHERE clause)

UPDATE ContactOwner co
INNER JOIN ContactOwner lkup
   ON lkup.ContactID = co.ContactID
SET co.AppUserIdOwner = -1
WHERE co.AppUserIdOwner = 1
AND lkup.AppUserIdOwner = 2
;

Open in new window

nfaria

If you don´t have CREATE TEMPORARY TABLES grant consider doing it in a 2-step like

SELECT GROUP_CONTACT(contactId SEPARATOR ',') AS dupsIds from ContactOwner where AppUserIdOwner = 2;

Store the value in a var strDupsIds and issue a

DELETE FROM ContactOwner WHERE AppUserIdOwner = 1 and ContactId IN (strDupsIds);

see
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

burtonrhodes

ASKER

That worked perfectly.