Solved

MySQL: How to update records with subselect in where clause

Posted on 2010-08-24
23
924 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best way to find a random number in a list of a million numbers 11 55
Amazon Redshift 2 39
SubQuery link 4 36
Combining Queries 7 28
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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