Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL: How to update records with subselect in where clause

Posted on 2010-08-24
23
Medium Priority
?
945 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 4
  • +1
23 Comments
 
LVL 59

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 59

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 59

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 59

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 59

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 59

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 59

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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 59

Expert Comment

by:HainKurt
ID: 33517704
mwvisa1,

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

0
 
LVL 60

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 60

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 60

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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