MYSQL: EXISTS Syntax help

Hi

Given the example bellow each post_id can have multiple meta_key & meta_values

For each post_id where both meta_key = 'keywords' AND meta_key = 'keyword'  I need to delete the row with meta_key = 'keywords'
so In the example rows 4 & 5 would go

Once I know either  meta_key = 'keywords' or meta_key = 'keyword' exists I can run an update to change  meta_key = 'keywords' to 'keyword'

CREATE TABLE wp_postmeta	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
);

INSERT INTO wp_postmeta VALUES
(1	,123	,'keyword'	,'xyz,abc,Something Else,Unique'),
(2	,124    ,'keyword'	,'xyz,abc,Something Else,Unique'),
(3	,125	,'keyword'	,'Experts, Exchange,Is Great,and Unique '),
(4	,123	,'keywords'	,'Hello World'),
(5	,124	,'keywords'	,'Hello World'),
(6	,126	,'keywords'	,'Experts, Exchange,Is Great,and Unique '),

Open in new window


Here is a select query for testing but isn't returning anything I'm expecting row 4 & 5

Select * FROM wp_postmeta WHERE meta_key = 'keywords' AND  meta_id in(
EXISTS (SELECT meta_id FROM wp_postmeta WHERE meta_key = 'keyword'and  meta_key = 'keywords' GROUP by post_id
        )
  )

Open in new window

LVL 1
trevor1940Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ste5anSenior DeveloperCommented:
hmm, meta_key = 'keywords' AND meta_key = 'keyword'  is a contradiction..
0
Máté FarkasDatabase Developer and AdministratorCommented:
SELECT * FROM wp_postmeta 
WHERE meta_key = 'keywords' 
AND  meta_id IN (
        SELECT meta_id FROM wp_postmeta 
        WHERE meta_key = 'keywords'
)

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
Pawan KumarDatabase ExpertCommented:
Please use this solution.

Note you have to delete first then update the records.
Post_id -> 125 AND 126 will be deleted.


CREATE TABLE wp_postmeta1	
(		
	 meta_id INT	
	,post_id INT	
	,meta_key VARCHAR(1000)	 
	,meta_value VARCHAR(1000)
);

INSERT INTO wp_postmeta1 VALUES
(1	,123	,'keyword'	,'xyz,abc,Something Else,Unique'),
(2	,124    ,'keyword'	,'xyz,abc,Something Else,Unique'),
(3	,125	,'keyword'	,'Experts, Exchange,Is Great,and Unique '),
(4	,123	,'keywords'	,'Hello World'),
(5	,124	,'keywords'	,'Hello World'),
(6	,126	,'keywords'	,'Experts, Exchange,Is Great,and Unique ')

DELETE T 
FROM wp_postmeta1 T
INNER JOIN 
(
	SELECT post_id
	FROM wp_postmeta1
	WHERE meta_key IN ('keyword','keywords')
	GROUP BY post_id
	HAVING COUNT(DISTINCT meta_key) <> 2
)k ON k.post_id = T.post_id 

UPDATE T 
SET T.meta_key = CASE WHEN T.meta_key = 'keywords' THEN 'keyword' ELSE T.meta_key END
FROM wp_postmeta1 T
INNER JOIN 
(
	SELECT post_id
	FROM wp_postmeta1
	WHERE meta_key IN ('keyword','keywords')
	GROUP BY post_id
	HAVING COUNT(DISTINCT meta_key) = 2
)k ON k.post_id = T.post_id

SELECT * FROM wp_postmeta1

Open in new window


OUTPUT AFTER DELETING AND UPDATING RECORDS

/*------------------------
SELECT * FROM wp_postmeta1
------------------------*/
meta_id     post_id     meta_key         meta_value
----------- ----------- ---------------- -------------------------------
1           123         keyword          xyz,abc,Something Else,Unique
2           124         keyword          xyz,abc,Something Else,Unique
4           123         keyword          Hello World
5           124         keyword          Hello World

(4 row(s) affected)

Open in new window

0
trevor1940Author Commented:
Thanx that worked

@Pawan
Post_id -> 125 AND 126 will be deleted.
No row bellow needed deleting

(4      ,123      ,'keywords'      ,'Hello World'),
(5      ,124      ,'keywords'      ,'Hello World'),
0
Pawan KumarDatabase ExpertCommented:
So you just needed was a select statement correct ?

So what is this
>>For each post_id where both meta_key = 'keywords' AND meta_key = 'keyword'  I need to delete the row with meta_key = 'keywords'
so In the example rows 4 & 5 would go


I also gave you the select ....You need to change it to select...:)

SELECT *
FROM wp_postmeta1 T
INNER JOIN
(
      SELECT post_id
      FROM wp_postmeta1
      WHERE meta_key IN ('keyword','keywords')
      GROUP BY post_id
      HAVING COUNT(DISTINCT meta_key) = 2
)k ON k.post_id = T.post_id
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
SQL

From novice to tech pro — start learning today.