Solved

MySQL: How to update records with subselect in where clause

Posted on 2010-08-24
23
920 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:burtonrhodes
  • 10
  • 8
  • 4
  • +1
23 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 33517481
try this

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

Author Comment

by:burtonrhodes
ID: 33517490
Still get the same error:

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

Author Comment

by:burtonrhodes
ID: 33517493
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)
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 51

Expert Comment

by:HainKurt
ID: 33517533
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)
0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 33517549
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.
0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 33517551
Just not sure how to solve this one in MySQL.  
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33517573
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)
0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 33517582
Not trying to make your job incredibly difficult, but I don't have the access to the database in order to "create" a view.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33517585
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)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33517599
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?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33517605
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
0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 33517608
yep.
0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 33517615
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)
0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 33517616
This is a bit of a bugger.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33517622
what about this? 33517585

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

I guess this is the only way to do that
0
 
LVL 3

Author Comment

by:burtonrhodes
ID: 33517631
Yeah - I will have to check if I have temp table privledges.  I will get back to you that one!
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33517693
Use a JOIN.
DELETE co
FROM ContactOwner co
INNER JOIN ContactOwner lkup
   ON lkup.ContactID = co.ContactID
WHERE co.AppUserIdOwner = 1
AND lkup.AppUserIdOwner = 2
;

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33517704
mwvisa1,

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

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33517709
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. :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33517728
Found one instance -- it used derived table and JOIN, so can try it like that also.
http://www.experts-exchange.com/Database/MySQL/Q_24817097.html
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33517777
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

0
 
LVL 4

Expert Comment

by:nfaria
ID: 33518809
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
0
 
LVL 3

Author Closing Comment

by:burtonrhodes
ID: 33521037
That worked perfectly.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now