dannyddd
asked on
Need MySQL query to update records where there is NOT a matching record from same table
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.
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.
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
)
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
)
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
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,
Maybe best in this instance to create and populate a temporary table and use that table for the update.
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.
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.
ASKER
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
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
ASKER
Thanks- this query needed optimising but the general approach worked.
set t1.content = NULL
where t1.data =544 and t2.data <> 543
AND t1.id = t2.id