Solved

MySQL: How to update records with subselect in where clause

Posted on 2010-08-24
23
903 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
Just not sure how to solve this one in MySQL.  
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Author Comment

by:burtonrhodes
Comment Utility
yep.
0
 
LVL 3

Author Comment

by:burtonrhodes
Comment Utility
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
Comment Utility
This is a bit of a bugger.
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
mwvisa1,

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

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
That worked perfectly.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

12 Experts available now in Live!

Get 1:1 Help Now