Need help with a MySQL Query

Hi,

in my Database I have a table called images. There I have two important columns: id and org_image_id. If a user modify an "original" image, the modified image is stored in the same table but with org_image_id = id of the original image.

What I want to do is to remove all modified images from the database, if there are no original image in the database. I wrote this query, but it does not work for me:

[SQL] delete FROM images WHERE org_image_id NOT IN(SELECT DISTINCT id FROM images WHERE org_image_id is NULL)and LRU < DATE_SUB(NOW(), INTERVAL 30 DAY);
[Err] 1093 - You can't specify target table 'images' for update in FROM clause

Open in new window


Can someone help me please?
LVL 1
LL0rdAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wolfgang_93Connect With a Mentor Commented:
Try something like this (note: the temporary table automatically is dropped at the end of
the user session):

CREATE TEMPORARY TABLE idlist (id INTEGER);
INSERT INTO idlist(id) SELECT DISTINCT id FROM images WHERE org_image_id IS NULL and LRU < DATE_SUB(NOW(), INTERVAL 30 DAY);
delete FROM images WHERE org_image_id NOT IN (select id from idlist);
0
 
tigin44Connect With a Mentor Commented:
As I understand this is not the whole query...This one relates to another.. if so could you provide the whole set...

delete FROM images
WHERE org_image_id NOT IN(SELECT DISTINCT id FROM images WHERE org_image_id is NULL)
     and LRU < DATE_SUB(NOW(), INTERVAL 30 DAY);
0
 
tigin44Commented:
also you can try this

DELETE I
FROM images I
      LEFT OUTER JOIN images D ON I.org_image_id = D.id
WHERE D.id IS NULL
  AND D.org_image_id is NULL
 AND I.LRU < DATE_SUB(NOW(), INTERVAL 30 DAY);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.