Solved

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

Posted on 2011-09-30
9
337 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

14 Experts available now in Live!

Get 1:1 Help Now