• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

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

0
matthewdacruz
Asked:
matthewdacruz
  • 3
  • 2
1 Solution
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
matthewdacruzAuthor Commented:
Perfect!

Thanks that did the trick
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now