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?
 
Máté FarkasConnect With a Mentor Database 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
 
ste5anSenior DeveloperCommented:
hmm, meta_key = 'keywords' AND meta_key = 'keyword'  is a contradiction..
0
 
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
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.