Solved

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

Posted on 2011-09-30
9
339 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

735 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