Solved

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

Posted on 2011-09-30
9
336 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dannyddd
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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)

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

10 Experts available now in Live!

Get 1:1 Help Now