Solved

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

Posted on 2011-09-30
9
338 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
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
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 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to join on ID, with prefix? 15 58
SQL Server 2012 r2 - calculations/operation on many Temp Tables 6 21
SQL Syntax 5 33
T-SQL: New to using transactions 9 18
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

815 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

6 Experts available now in Live!

Get 1:1 Help Now