Mysql delete syntax error

Hi Experts
I have a syntax error in my delete statement and cant figure out why.

Basically the nested statement gives me the list of items I do not want deleted. All other items not not found in the nested select list should be deleted.

[Err] 1093 - You can't specify target table 'theme_keywords_pyramidpromo_assigned' for update in FROM clause
DELETE FROM
theme_keywords_pyramidpromo_assigned
WHERE
theme_keywords_pyramidpromo_assigned.PP_ID_assigned NOT IN (
		SELECT
		theme_keywords_pyramidpromo_assigned.PP_ID_assigned
		FROM
		theme_keywords_pyramidpromo_assigned ,
		articles
		WHERE
		theme_keywords_pyramidpromo_assigned.profileid = 131 AND
		theme_keywords_pyramidpromo_assigned.PP_ID_assigned = articles.PP_ID_assigned

) AND theme_keywords_pyramidpromo_assigned.profileid = 131

Open in new window

matthewdacruzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:
does the select query below returns any row? I think no.... you should check the where condition...

SELECT *
FROM  theme_keywords_pyramidpromo_assigned
WHERE theme_keywords_pyramidpromo_assigned.PP_ID_assigned NOT IN (
            SELECT
            theme_keywords_pyramidpromo_assigned.PP_ID_assigned
            FROM
            theme_keywords_pyramidpromo_assigned ,
            articles
            WHERE
            theme_keywords_pyramidpromo_assigned.profileid = 131 AND
            theme_keywords_pyramidpromo_assigned.PP_ID_assigned = articles.PP_ID_assigned

) AND theme_keywords_pyramidpromo_assigned.profileid = 131
0
matthewdacruzAuthor Commented:
Yes the nested select returns the ID if the row I want to keep.
Example
530
514
532
582
631
493
These are the ids I do not want deleted.
0
matthewdacruzAuthor Commented:
Your query returns rows as well
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ralmadaCommented:
try like this
DELETE FROM
theme_keywords_pyramidpromo_assigned
WHERE
theme_keywords_pyramidpromo_assigned.PP_ID_assigned NOT IN (
		select PP_ID_Assigned from (
			SELECT
			a.PP_ID_assigned
			FROM
			theme_keywords_pyramidpromo_assigned a,
			articles b
			WHERE
			a.profileid = 131 AND
			a.PP_ID_assigned = b.PP_ID_assigned
		)
) AND theme_keywords_pyramidpromo_assigned.profileid = 131

Open in new window

0
ralmadaCommented:
missed something there
DELETE FROM
theme_keywords_pyramidpromo_assigned
WHERE
theme_keywords_pyramidpromo_assigned.PP_ID_assigned NOT IN (
		select PP_ID_Assigned from (
			SELECT
			a.PP_ID_assigned
			FROM
			theme_keywords_pyramidpromo_assigned a,
			articles b
			WHERE
			a.profileid = 131 AND
			a.PP_ID_assigned = b.PP_ID_assigned
		) as t1
) AND theme_keywords_pyramidpromo_assigned.profileid = 131

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matthewdacruzAuthor Commented:
Perfect!

Thanks that did the trick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

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.