Solved

Need MySQL query to update records where there is NOT a matching record from same table

Posted on 2011-09-30
9
341 Views
Last Modified: 2012-08-14
Hello,

I have a MySQL table (simplified) with the following columns

id         data        content
25       543          abc
27       543          abc
28       543          abc
29       543          abc
25       544          abc
26       544          abc
27       544          abc
28       544          abc
29       544          abc
30       544          abc
31       544          abc

I am trying to find a query which updates the "content" column (setting it as NULL) where data= 544 AND where there is not a matching record for that id where data =543.

In other words, something like

UPDATE  mytable t1 SET content=NULL
WHERE  `data` =544
AND NOT
EXISTS (
SELECT id
FROM  mytable t2
WHERE  `data` =543
AND t1.id = t2.id
)

However, the above query does not execute because of MySQL error #1093 - You can't specify target table 't1' for update in FROM clause ( I think MySQL does not allow you to run an update on a table you are simultaneously running a select on).

If anyone can suggest an alternative way of running this query please let me know. NB the table itself contains millions of rows so the query needs to perform efficiently.
0
Comment
Question by:dannyddd
[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
9 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 36891092
Update mytable t1 , mytable t2

set t1.content = NULL

where t1.data =544 and t2.data <> 543
AND t1.id = t2.id
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36891099
Perhaps:

UPDATE  mytable SET content=NULL
WHERE  `data` =544
AND NOT
EXISTS (
SELECT id
FROM  mytable as mytable2
WHERE  `data` =543
AND mytable2.id = mytable.id
)

0
 

Author Comment

by:dannyddd
ID: 36894322
I'm afraid none of the above solutions work.

pratima_mcs query returns all rows where t2.data<>543 (ie where t2.data has other values), not the rows where there is no t2.data=543.

Jacobfw query does not work for the same reason as my one "#1093 - You can't specify target table 'mytable' for update in FROM clause"

Does anyone have any other suggestions? Thanks

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Accepted Solution

by:
Jacobfw earned 500 total points
ID: 36894364
Here is the workaround I think:

http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

UPDATE  mytable SET content=NULL
WHERE  `data` =544
AND NOT
EXISTS (
SELECT id
FROM (select * from  mytable) as mytable2
WHERE  `data` =543
AND mytable2.id = mytable.id
)

0
 

Author Comment

by:dannyddd
ID: 36894561
Hi Jacobfw.

Thanks. I had already tried that workaround. I've just tried it again. Both times the query seemed to get stuck. I killed it the first time after several hours, and just let it run again for 45 minutes before killing it.

I need something that performs much better, bearing in mind the large dataset I am working with.

Keen to hear any other ideas,
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36894627
Maybe best in this instance to create and populate a temporary table and use that table for the update.
0
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 36899040
you have given the sample data set. i.e.
id         data        content
25       543          abc
27       543          abc
28       543          abc
29       543          abc
25       544          abc
26       544          abc
27       544          abc
28       544          abc
29       544          abc
30       544          abc
31       544          abc

but after update give the data set to understand the problem and i try it if any workaround exists.

0
 

Author Comment

by:dannyddd
ID: 36900756
Actually, I have managed to get the workaround Jacobfw provided above to work, but when the subquery results are limited with a WHERE clause - as below.

Without this, as mentioned, performance issues killed this query.

UPDATE  mytable SET content=NULL
WHERE  `data` =544
AND NOT
EXISTS (
SELECT id
FROM (select * from  mytable WHERE  `data` =543) as mytable2
WHERE mytable2.id = mytable.id
)

Thanks for all the help
0
 

Author Closing Comment

by:dannyddd
ID: 36900758
Thanks- this query needed optimising but the general approach worked.
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

688 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