Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL: How to update records with subselect in where clause

Posted on 2010-08-24
23
Medium Priority
?
968 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 61

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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 61

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 61

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 61

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 61

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 61

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 61

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 61

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 61

Expert Comment

by:HainKurt
ID: 33517704
mwvisa1,

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

0
 
LVL 61

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 61

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 61

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

576 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